Compare Date IN VB6 Using SQL Query

[sheldondsouza]

Have been creating this application and is almost done but ran into a problem

In the database the table Attendance has a field Date and its in the format text, so what i am doing basically is comparing the two dates one date is displayed in the form as "MM/DD/YYYY" or eg 02/15/2007

so when comparing this one with the one in the databse using the code below, i populate the grid and works fine.

sql3000 = "select * from Attendance where TherapistName like '" & (Combo4.Text) & "%' And Date = '" & (Text18.Text) & "' Order by Attendance_Id "

_____________

However when creating reports it was mandatory for me to change the date field in the database from text to date/time and the date previously in the field changed form 02/15/2007 to 2/15/2007

Since the field was change, i change the sql query to as it gave and error i removed the single quotes as the field was not text

sql3000 = "select * from Attendance where TherapistName like '" & (Combo4.Text) & "%' And Date = " & (Text18.Text) & " Order by Attendance_Id "

but nothing works! i thought propbaly in the form the textbox is shown as 02/15/2007 and is not same in database format so changed the way the text was shown in the form and tried comparing but still no use!

It works fine when its text , but i need it using date/time format

Please help project is stuck here i need to complete this as soon as possible

[oracle128]

What database server are you using?

Usually, date fields should be referred to with surround single quotes (hashes in Access), and using the format 'YYYY-MM-DD' is the safest.

[degsy]

In Access you can use Format() to get the datetime in a format of your choice.
In SQL Server you can use Convert or Cast.

[sheldondsouza]

Never mind, i just made the date the primary key and any duplicate entires will give a message box stating that entry for today is done