Struggling with DLookUp and date formats

[micks55]

I am using DLookUp as the Control Source of a "PayWeek" unbound control and I can see that this fails because of Access defaulting to mm/dd/yyyy but I can't find a way to fix it.

I have searched the forum and found lots from Bob, George, Mile-O and others so thanks to all but although I have tried lots of what looked like promising solutions, I still can't work it out.

This is the control source that returns only some good results.
=IIf(Not IsNull([PayDate]),DLookUp("WkID","PayrollWeeks","StartDate <=#" & Format([PayDate],"dd/mm/yyyy") & "# And EndDate >=#" & Format([PayDate],"dd/mm/yyyy") & "#"),0)

Dates are the UK tax year (Apr-Apr). The "PayrollWeeks" table has WkID, StartDate, EndDate(both are Short Date format).

Entries in the unbound PayDate field bring these results.
04/12/2008 (4 Dec) returns week 1 when I should get week 35.
11/02/2009 (11Feb) fails to return anything because the table ends on 5 Apr 2009.
18/02/2009 (18Feb) correctly returns week 46.

I have tried to Format() both the table field ([StartDate],"dd/mm/yyyy") AND the criteria field ([PayDate],"dd/mm/yyyy") within the DlookUp but my syntax is rejected.

Any suggestions would be greatly appreciated.
Thanks for your time, Mike

 

[MStef]

Try with FORMAT "mm-dd-yyyy"

 

[micks55]

Thanks MStef and sorry to waste peoples time.

I just found the answer by Jon Holmes in a 2001 thread "Access 2000 date madness" who said "When working in code, programmers whose regional settings are non-US, must explicitly convert all dates to US format."

I have changed the (2) Formats to "mm/dd/yyyy" in the DLookUp and all is well.