DLookup expression & its SQL code 1

[Caryisms]

Can someone please explain to me DLookup's components and how to write the SQL code?

 

[JimHorton]

caryisms,
You just put, in double quotes the 3 arguments:
x = Dlookup("fieldname","table_or_Query_name","Criteria without WHERE&quot

ie:
x = Dlookup("CustName","tblCustomers","CustID = 100&quot

If you're using this in code, and have a variable for the criteria (or field and tablename, for that matter), do this:
x = Dlookup("CustName","tblCustomers","CustID = " & variable)

If custID is a string:
x = Dlookup("CustName","tblCustomers","CustID = '" & variable & "'&quot

You can put multiple criteria, ie
x = Dlookup("CustName","tblCustomers","CustCity = '" & variable & "' AND Gender = 'M'&quot

Remember, that 'x', or the return value, must be able to accept a NULL. So either make x a variable, or wrap dlookup in NZ(), ie:
dim x as string
x = nz(Dlookup(&quot;CustName&quot;,&quot;tblCustomers&quot;,&quot;CustCity = '&quot; & variable & &quot;' AND Gender = 'M'&quot,&quot;<Not Found>&quot

--Jim

 

[lancelessler]

Jim, Thanks for your response to the question about using DLookup. I tried to find official documentation about this and other domain aggregate functions without success. Isn't there official Microsoft documentations somewhere for describing these functions? Alternatively, is there an unofficial location for all Access documentation? I can't believe there isn't a reference to these functions in the online help that comes with Access.

 

[n4467r]

Try this link

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/off2000/html/acfctDLookup.asp

Good Luck!

David
N4467r@comcast.net