Performing a Double Lookup in Microsoft Access Requiring Multiple Criteria 

Domain functions (Dlookup, dcount, dmax, dsum, etc) are actually offshoots of SQL queries. So, the way I write my Dlookup is this: =DLOOKUP("CreditLimit", "Customer", "State= ' " & State & " ' " & " AND City= ' " & City & " ' ") If this is unpacked it results in something like: SELECT CreditLimit FROM Customer WHERE State ='Buffalo' AND State = 'NY'. Classic SQL. Once you mimick SQL, writing the multiple criteria should be a breeze. Single quote is more readable for the domain functions. Thanks Richard for your valuable contents always.

Brilliant. Working on a project to have access do a monstrous amount of calculations that we do in Excel. The reason was, no one knew how to do the lookup from one table to the other because the parent table didn't have a value for all in the child, so Access was culling down the child table to just values that are in both tables. So now we can do the calculations in Access and just export the values to Excel.

Thanks. Using dlookup in query was required in my case as my form was continuos form and using dlookup in such structure slowed down my form even more than query. Didnt use join in query as it caused my query unupdateble.

Thank You

10/10

I cant get this to work where the first criteria is a date and the second is an ID field. What should the syntax be for that senario ?

Nice video, but as u said i done this work in quarry and its slow vvvv slow

I like to use single quotes because multiple double quotes (or triple plus when concatenating) turn me cross eyed. DLookup("CreditLimit", "CustomerT", "City = '" & [City] & "' AND State = '" & [State] & "'") Or DLookup("CreditLimit", "CustomerT", "City = 'Buffalo' AND State = 'NY' ") Or to avoid that all together I made a function that returns a string wrapped in single quotes: Dlookup("CreditLimit", "CustomerT", "City = " & qt(City) & " AND State = " & qt(state))

Can you show it by query plz