Dlookup Date issue

[abbers_01]

I am using a dlookup to find a record this is my code:

DLookup("[Total Hours]", "TimeCard", "[Employee]=" & "'" & [employeeTextBox] & "'" & " AND [Job Date] =" & "#" & [tuesdaytxt2] & "#")

tuesdaytxt2 is a date text field with a medium date format (day/month/year) i.e. May 1/08 = 01/05/08
but for some reason it needs to be (month/day/year).
i.e. May 1/08 = 05/01/08

What is really strange is that it worked up until May 1/08, so for some reason at May 1st the format that Dlookup works on changed to month/day/year.

Anyone come across this and perhaps have any ideas on how to rectify this.

Thanks.

 

[RuralGuy]

Maybe this link will clear things up a bit for you.

 

[ecawilkinson]

try:
DLookup("[Total Hours]", "TimeCard", "[Employee]=" & "'" & [employeeTextBox] & "'" & " AND [Job Date] =" & clng([tuesdaytxt2]))

this works if the tuesdaytxt2 is formatted as a date not a string

HTH,
Chris