Dlookup with Date format

[AndrewKent]

Hi there,

Just noticed that a Dlookup I am using on a field with dates is not working correctly. The two date fields that I have are formatted "dd/mm/yyyy" however when I use the following Dlookup:

PERIOD: DLookUp("[DateMatrix_Period]","[tblMatrix_DateMatrix]","[DateMatrix_Date]=#" & [IntL_DateofLeaving] & "#")

I can see that the date formatting is getting a bit muddled (so for example 31/09/2011 remains as such but 10/09/2011 is being changed to 09/10/2011 which is mm/dd/yyyy)

How do I update this query to keep the date format as dd/mm/yyyy?

Kind reagrds,

Andy

 

[boblarson]

You need to use U.S. date formatting for querying (you can display the way you want but the criteria must be in a non-ambiguous format).

Try this instead:

PERIOD: DLookUp("[DateMatrix_Period]","tblMatrix_DateMatrix","[DateMatrix_Date]=" & Format([IntL_DateofLeaving], \#mm\/dd\/yyyy\#"))