Dlookup - combine multiple criteria syntax in query (1 Viewer)

:banghead: I am trying to create a Dlookup in Access 2010 within a query using query wizard. I want to lookup the tax rate for an employee based on a salary range and their 'tax category' (string). Through troubleshooting I can get the criteria to work separately. These are:
These work and return the correct values for each column/row when I run the query.

However, when I combine the criteria (using the build wizard) as follows:

The Dlookup will returns 0 values but will not give an error message.

I've tried quite a few variations on syntax and quotes and so on. However, it's just not working for me. :banghead:

(Note: I'm an accountant and not comfortable with VBA modules. I've looked through a whole bunch of threads and forums and just not getting to the right answer)

Can anyone please help?

Hello woodburym, Welcome to AWF..

Check out this link on how to use DLookup multiple criteria..

The 'AND' portion of your criteria (where you connected them) needs to be part of the string. As you have it now, its acting like a variable.

Expr1: DLookUp("Base","TABWT","[TABWT].[Taxclass] = '" & [FirstOfTaxGroup] & "' AND " & [grosspay] & " Between" & [TABWT]![Minimum] And [TABWT]![MaxBracket]")

You ended the double quotes too soon. Since you are combining criteria you need to include the 'AND' within the double quoted area. If you look closely above you will see that that the double quote after the single quote is gone and now placed after the AND.

Plog, you are a Legend! Thank you for the explanation. I understand it now. I only had to add one additional quote to get it past a string error. But thanks to your explanation it was not a problem.



The final working solution is below with an additional double quote in red:

Expr1: DLookUp("Base","TABWT","[TABWT].[Taxclass] = '" & [FirstOfTaxGroup] & "' AND " & [grosspay] & " Between" & "[TABWT]![Minimum] And [TABWT]![MaxBracket]")