Convert String to Integer Issue

[theflyingminst]

Hi, I am trying to order a char column in my database as integers and I keep getting this error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Undefined function 'to_num' in expression

I've so far tried these three options and they all call it.

1. ORDER BY CAST(thecolumn AS int)
2. ORDER BY to_num(thecolumn)
3. ORDER BY Convert(int, thecolumn)

Is this even really possible to do or do I have to convert the column to a numeric field?

Any help is much appreciated.

Thank you

[chazzy]

maybe
CAST(theColumn AS Integer) ? access uses cast to do conversion; though i don't know what might be happening in the order by.

[theflyingminst]

Hi Chazzy, that helped ALOT.

I'm getting a different error now though, I think my syntax might be a little off.

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'CAST(Miles AS Integer)'.

My actual code: ORDER BY CAST("&sort &" AS Integer) ASC

Thanks so much

[theflyingminst]

Hi I was poking around other forums and this seems to do the trick actually:

CLng(thecolumn)

Thanks so much Chazzy