Date Picker - correct data type

[gibben]

Hi there,
Just a bit curious on using dates in a database, I am using dates a lot through my database with the MSComCtl2.DTPicker object and within the tables I have the datatype set to date/time with the default value being date().

All works well except for when I try to compare a date in the database to the value in the DTPicker on a form, it tells me I have invalid datatypes. If I change the datatype in the table to text with a default value of date() then it seems to compare them OK, so then I can see if a particular date exists ....

The users of the database really like the DTPickers so it isn't an option to ditch them .........

This is how I am comparing them with the datatype as text:
rs.FindFirst "[TS_Test_Subject_ID] = " & Str(Nz(Me![TS_Test_Subject_ID_Combo], 0)) & "AND [Start_Date] = " & Me![DTPicker1] & "'"
and it compares fine.....

Will functions like datediff still work with the dates stored as text ??

Any recommendations on how I should set up the date comparison and storage within the table would be appreciated.

 

[FancyPrairie]

I would store the dates in the tables as dates and chanage your find first statement to this:

rs.FindFirst "[TS_Test_Subject_ID] = " & Str(Nz(Me![TS_Test_Subject_ID_Combo], 0)) & "AND [Start_Date] = #" & CDate(Me![DTPicker1]) & "#"

 

[gibben]

Thanks for the quick reply,

That was the little snippet that I needed to fix my problem.

Thank You