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.
Excellent point. A lot of people have brought this up. I actually have a video coming out on why I don't like using single quotes ' in my Access databases. Stay tuned.
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.
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))
Excellent point. A lot of people have brought this up. I actually have a video coming out on why I don't like using single quotes ' in my Access databases... and with a function like you have. Stay tuned.