Error in Expression Syntax Error

[Perfac]

Me!Wek_ID041 = ("WekID052","q01Week","#Format(Me!PublicHolidayDat e041, "yyyy-mm-dd")# & " Between(FstDayOfWeek052 And LstDayOfWeek052")



I am trying to get the correct expression to get the correct week ID from query q01week.

[Perfac]

Sorry, wrong query, this is the correct query.

[June7]

Everything on the right side of = sign is nonsense. Are you trying to do a DLookup()?

If you want the week number of a date value, consider:

DatePart("ww",Date())

or

Format(Date(),"ww")

The first returns a number, the second returns a string.

[Perfac]

I send a reply a minute ago, now I don't see it. I am short of sleep, bad concentration errors. Partner came in just now and fixed it, it works.

Me!Wek_ID041 = DLookup("WekID052", "q01Week", "#" & Format(Me!PubHolidayDate041, "yyyy-mm-dd") & "# Between [FstDayOfWeek052] And [LstDayOfWek052]")

[isladogs]

Are you absolutely sure that filter criteria works for all dates?
The date formatting should be applied to the date range and not the field itself

To my mind it should look like this:

Me.Wek_ID041 = DLookup("WekID052", "q01Week","PubHolidayDate041 Between #" & Format([FstDayOfWeek052],"yyyy-mm-dd") & "# And #" & Format([LstDayOfWeek052],"yyyy-mm-dd") & "#")

I'm writing this on a tablet but hopefully no errors above

[Perfac]

Thank you sir. Yes it clearly works. I copied it from where it works to paste it here, no more concentration errors.

[isladogs]

OK but I'm surprised if all values really are correct as what you wrote is not the correct way to apply formatting to date ranges
Suggest you check for several date ranges in the first 12 days of the month e.g. between 08-May-2008 and 02-Jul-2008

What if someone uses a short date format e.g. 05/07/2008?

[Perfac]

My son and partner Ruben set it up that way. I showed him your opinion and he thinks your is more correct and he doesn't understand why his works. We have on our wall a list of expressions that comes up frequently. Yours is now on that list and when needed again that will be the way. The format part in the expression was suggested 2 years ago by someone in a different expression and we always use dd-mmm-yyyy in our input fields, but it doesn't matter in what format we enter it works.