I have created a dlookup which looks for 11/02/2009 in the table "period". The results are then set in the variable per. However, the message box returns 02/11/2009.

The format in the table is dd/mm/yyyy and queries work fine.

My windows locale is uk.

Dim per As String
per = DLookup("perdate", "Period", "Format([perdate], 'dd/mm/yyyy') = #11/02/2009#")
MsgBox per



How can I get it to lookup in uk format?

I suspect you'll have to format the message box output, which probably defaults to US format. I think it's looking up correctly, just not displaying correctly.

unfortunately not, I only put in to a message box so I could demonstrate it better.

I have changed the return field to bring back the id number, which is clearly showing that it looks up the november date rather than 11th feb.

I've never needed to worry about it, since I'm in the US, but maybe this helps?

http://support.microsoft.com/default.aspx?scid=kb;en-us;210069

Bottom line, I think you'll need to use US format in code.

thanks for the reference link.

nice document from microsoft explaining how to work around something that should work in the first place ;)

Ive found it easier to create a table with todays date and join to that in a query. I can then code to look at the date in the query rather than the vba.date

works that way for us brits.

really appreciate your help though - thank you