I've been looking for hours...

[hbrems]

Can you figure out what's wrong with this code? I want to retrieve the ID that matches the date...

vchecklist = DLookup("fldchecklistid", "tblchecklist", "flddate = " & "#" & Me.txtdate & "#")
msgbox vchecklist

Kind regards,
Hans

 

[RuralGuy]

Does this link help at all? Return Dates in US #mm/dd/yyyy# format

 

[gemma-the-husky]

if its a uus/uk date issue wrap the date with format(txtdate,"long date")

 

[hbrems]

I'm not sure if that's the problem. But somehow I can't see anything else being wrong with the code. This is really frustrating me now as it is holding me back to continue on my project...

 

[RuralGuy]

Have you tried adjusting your code to US dates?

 

[hbrems]

The code looks like this now :

Const JetDateTimeFmt = "\#mm\/dd\/yyyy hh\:nn\:ss\#;;;\N\u\l\l"
Const JetDateFmt = "\#mm\/dd\/yyyy\#;;;\N\u\l\l"
Const JetTimeFmt = "\#hh\:nn\:ss\#;;;\N\u\l\l"

vcheck = DLookup("fldchecklistid", "tblchecklist", "flddate = " & "#" & Me.txtdate & "#")
MsgBox vcheck

I don't know how to refer to the new formats in my Dlookup though. Thanks for the replies so far.

 

[hbrems]

Hey guys, I solved the problem

Here's what I needed:

vcheck = DLookup("fldchecklistid", "tblchecklist", "flddate = #" & Format(Forms![frmmain]![txtdate], "yyyy-mm-dd") & "#")

Thanks for the swift replies, turns out you guys were right ;-)

 

[RuralGuy]

Outstanding! Thanks for posting back with your success.