Expression Between dates

[Perfac]

I'm embarrassed to be back with something similar that took more than 50 post. A reminder the expression below works well.



VatRate03: DLookUp("[VatRate01]","t02VatRate","VatCode01='" & [VatCodePurchases01] & "' And #" & Format([DocumentDate01],"yyyy-mm-dd") & "# Between [StartDate11] And [EndDate13]")

Don't know why I battle to find the next one without an error. I am trying;
Wek_Id03: Dlookup(" [WekID] ","q01Week",#" & Format([PubHolidayDate01]), "yyyy-mm-dd") & "# Between [StartDate13] And [EndDate10]")

Throughout my application I am trying to spare every possible entry or click of the mouse that a user don't need to make. In this example; PubHolidayDate01 is entered in q01publicholidays and I need the expression in green to return the WekID where PubHolidayDate01 falls between StartDate13 and Enddate10 from q01 week. The error. "The expression you entered has an invalid date value.

[Perfac]

Ignore the image in blue.

[isladogs]

You're having problems because you are making the same syntax errors as before:
This SHOULD work:

Wek_Id03: Dlookup("WekID","q01Week","PubHolidayDate01 Between #" & Format(StartDate13,"mm/dd/yyyy") & "# And  #" & Format(EndDate10,"mm/dd/yyyy") & "#")

OR if preferred

Wek_Id03: Dlookup("WekID","q01Week","PubHolidayDate01 Between #" & Format(StartDate13,"yyyy-mm-dd") & "# And  #" & Format(EndDate10,"yyyy-mm-dd") & "#")

However, as I suggested the same syntax last time, I won't be surprised when you say it doesn't!!!

NOTE:
1. your fields have no spaces so I've removed unnecessary square brackets
2. I've removed the space either side of WekID
3. The Date formatting needs to be on the StartDate & EndDate as shown

P.S. I ignored both images

[Perfac]

Most of the time when I copy expressions from here into my system it works. This one leaves a blank, no error message. My Windows system and date format of choice is Short date dd-MMM-yyyy, including table t01week and t01Publicholidays. The previous big issue with that expression was not the format of the date since yyyy-mm-dd is working right now on the other one. I recreated t01week and the date formats, no luck yet.

[isladogs]

Suggest you upload sample data for someone to test it.
The syntax you ended up with from your previous thread is non standard and I believe it will not work for all dates.
By contrast, the syntax I provided is the accepted syntax which should therefore work.
It doesn't matter what your default date format is n Windows or Access as dates are actually stored as double number datatype.