Getting a value from a table: DLookup()
Sooner or later, you will need to retrieve a value stored in a table. If you regularly make write invoices to companies, you will have a Company table that contains all the company's details including a CompanyID field, and aContract table that stores just the CompanyID to look up those details. Sometimes you can base your form or report on a query that contains all the additional tables. Other times, DLookup() will be a life-saver.
DLookup() expects you to give it three things inside the brackets. Think of them as:
Look up the _____ field, from the _____ table, where the record is _____
Each of these must go in quotes, separated by commas.
You must also use square brackets around the table or field names if the names contain odd characters (spaces, #, etc) or start with a number.
This is probably easiest to follow with some examples:
- you have a CompanyID such as 874, and want to print the company name on a report;
- you have Category such as "C", and need to show what this category means.
- you have StudentID such as "JoneFr", and need the student?s full name on a form.
Example 1:
Look up the CompanyName field from table Company, where CompanyID = 874. This translates to:
=DLookup("CompanyName", "Company", "CompanyID = 874")
You don't want Company 874 printed for every record! Use an ampersand (&) to concatenate the current value in the CompanyID field of your report to the "Company = " criteria:
=DLookup("CompanyName", "Company", "CompanyID = " & [CompanyID])
If the CompanyID is null (as it might be at a new record), the 3rd agumenent will be incomplete, so the entire expression yields #Error. To avoid that use Nz() to supply a value for when the field is null:
=DLookup("CompanyName", "Company", "CompanyID = " & Nz([CompanyID],0))
Example 2:
The example above is correct if CompanyID is a number. But if the field is text, Access expects quote marks around it. In our second example, we look up the CategoryName field in table Cat, where Category = 'C'. This means the DLookup becomes:
=DLookup("CategoryName", "Cat", "Category = 'C'")
Single quotes within the double quotes is one way to do quotes within quotes. But again, we don't want Categoy 'C' for all records: we need the current value from our Category field patched into the quote. To do this, we close the quotation after the first single quote, add the contents of Category, and then add the trailing single quote. This becomes:
=DLookup("CategoryName", "Cat", "Category = '" & [Category] & "'")
Example 3:
In our third example, we need the full name from a Student table. But the student table has the name split into FirstName and Surname fields, so we need to refer to them both and add a space between. To show this information on your form, add a textbox with ControlSource:
=DLookup("[FirstName] & ' ' & [Surname]", "Student", "StudentID = '" & [StudentID] & "'")
Quotes inside quotes
Now you know how to supply the 3 parts for DLookup(), you are using quotes inside quotes. The single quote character fails if the text contains an apostrophe, so it is better to use the double-quote character. But you must double-up the double-quote character when it is inside quotes.
ELookup()
If you are interested in an extended DLookup() that operates faster, handles nulls correctly, and lets you to specify which of several possible matches you want returned, see:
ELookup(). Suits Access 95 and later.
.jpg)
