TO_NUMBER SQL function not work

Hai All,

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

Code:

---

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

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.

Code:

---

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
 
...

---

Hai Norie,

Very thanks for your reply

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

Code:

---


month([date])

---