Oracle sql date error....

Please the code below:
date1 = Format(Text1.Text, "dd/mmm/yy")
date2 = Format(Text2.Text, "dd/mmm/yy")
rs.Source = "select * from SaleHead where shcustno = '" & Combo1.Text & "' and OrderDate >= '" & date1 & "' AND OrderDate <= '" & date2 & "'"
Set rs.ActiveConnection = cn
rs.Open
:-------------------------------------------:
Please see the date1 to date2 statement
I've also tried #" & date1 & "#. It dosen't work at all.

Please

-Khamal-

[ting2000]

Hi there,
I don't think there is anything wrong with your sql. I used your sql and changed only the table name and field name and it worked fine. This is how I do it:

date1 = Format(Text1.Text, "dd/mmm/yy")
date2 = Format(Text2.Text, "dd/mmm/yy")
sSQL = "select * from table_name where "
sSQL = sSQL & "created_date >= '" & date1 & "' created_date <= '" & date2 & "'"
Set rs = g_cn.OpenResultset(sSQL, rdOpenStatic, rdConcurReadOnly)

Don't know if this applies to you but maybe you could give it a try ...

Ting2000

[ting2000]

Oops!
There is an "AND" missing in the sql.

Sorry.

date1 = Format(Text1.Text, "dd/mmm/yy")
date2 = Format(Text2.Text, "dd/mmm/yy")
sSQL = "select * from table_name where "
sSQL = sSQL & "created_date >= '" & date1 & "' AND created_date <= '" & date2 & "' "
Set rs = g_cn.OpenResultset(sSQL, rdOpenStatic, rdConcurReadOnly)

[Marita_l]

I found this at an ORACLE SQL reference site this is the correct format for an ORACLE date. ORACLE uses - not \.

DATE datatype
A standard Oracle datatype used to store date and time data. Standard date format is DD-MMM-YY, as in 01-JAN-89. A DATE column may contain a date and time between January 1, 4712 BC to December 31, 4712 AD.

Try this maybe it will work. Otherwise you may have to TO_DATE the field as it is passed to ORACLE this will slow down the system though as it by passes all indexes.

Thank you Ting and Marita....

To_Date, function....successfull


I haven't try Ting's code yet..

-Khamal-