TO_NUMBER SQL function not work

[mohanvijay]

Hai All,

I try to get recordset from access table like month as number by using TO_NUMBER but did not work

qqqry = "SELECT ID,TO_NUMBER(format(date," & Chr(34) & "mm" & Chr(34) & ")) AS mymonth FROM date_test"
rs.Open qqqry, db, adOpenStatic, adLockReadOnly

error occurs "UNdefined function 'TO_NUMBER' in expression

how can i solve it

please help

[Norie]

What do you actually have in the table and what do you actually want to return?

As far as I'm aware there is no such function as TO_NUMBER in Access.

Isn't it actually an Oracle SQL function?

There are plenty of date/numeric functions that you could use.

Which to use is hard to tell without knowing what you have and what you want.

Here are a couple of examples, assuming your field name is mm.

Month as number to month name.
 
MonthName(mm) As MyMonth
 
Format("1 " & mm & "2011", "mmmm") As MyMonth
 
Month number from month name:
 
Month(DateValue ("1" & mm & "2011") As MyMonth
 
Month number from date:
 
Month(mm) As MyMonth
 
...

[mohanvijay]

Hai Norie,

Very thanks for your reply

Actualy i want to get month of particular fled now i get that by

month([date])