View Full Version : TO_NUMBER SQL function not work

05-04-2011, 12:08 AM
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

05-07-2011, 05:08 AM
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


05-07-2011, 08:28 AM
Hai Norie,

Very thanks for your reply

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