Error in Expression

[Perfac]

PayeBelow65b: Nz(DLookUp("[YoungerThan65]","q03PAYEWeekly",[Wage075b] & " Between [StartAmount065b] And [EndAmount065b] And #" & Format([WageDate075],"yyyy/mm/dd") & "# Between [StartDate065b] And [EndDate065b]"),0)



YoungerThan65 - Currency
Wage075b - Currency
StartAmount065b - Currency
EndAmount065b - Currency
WagesDate075 - Date
StartDate065b - Date
EndDate065b - Date

I haven't encountered any errors with this code until recently and have no clue what has changed, but received the following error when trying to use the field on a report and displays as "#Error" in the query records when Wage075b has a decimal value

"Syntax Error (comma) in query expression '1562,5 Between [StartAmount065b] And [EndAmount065b] And #2017/01/06# Between [StartDate065b] And [EndDate065b]"

I think the problem lies within Wage075b not being 1562.5(with a period and not a comma)

I have tried controlling the format with Ccur() but it hasn't changed anything.

[ranman256]

would it be easier to use a query using these params?
but
what about converting every field outside the quotes, (like you did the FORMAT part):

Nz(DLookUp("[YoungerThan65]","q03PAYEWeekly",[Wage075b] & " Between " & me.StartAmount065b & " And " & me.EndAmount065b & " And #" & Format([WageDate075],"yyyy/mm/dd") & "# Between " & me.StartAmount065b & " And " & me.EndAmount065b ),0)

and do you need the FORMAT? A date field should be usable without a format since it is a date.

[Minty]

If you have even a smallish amount of records this would be much more efficient if you joined the table with the rates into the query directly.
A dlookup() in a query will cause a seperate background query to run for each record. Very slow and inefficient.