Dlookup date criteria wrong

0
votes

I use a Dlookup with a date criteria. With some dates this works fine, with other dates it returns a #error. I can not figur out what I am doing wrong.

=Nz(DLookUp("[TSB]";"tblCTLATL";"[Day]=#" & Format([cboActivityDate];'Medium Date') & "#"))

In my opinion it has something to with the formatting. Can anyone help me?

1
ms-accessvba
Can you edit your question and give us examples of dates which work and dates which do not work? - Lajos Arpad
Like dates in october and march didn't work, But september and august works fine. - C.F. Aarssen
He means that you edit and to create a minimal example - 5th

1 Answers

1
votes

Medium Date is not an appropriate date format for a date literal. The appropriate formats are yyyy-mm-dd and mm/dd/yyyy.

Adjust your Format function:

=Nz(DLookUp("[TSB]";"tblCTLATL";"[Day]=#" & Format([cboActivityDate];'yyyy-mm-dd') & "#"))