Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.


Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.


DLookup Function Examples by BobStubbs
Posted: 24 Mar 04 (Edited 30 Mar 04)

This FAQ contains some examples of DLookup syntax used in various places within Access 2000.  No 'rocket science', but when I was starting out with Access I often struggled for some time to get this syntax correct.

What does DLookup do?

Retrieves a value from a table, based on another field value taken from a form, report or variable.

Example #1 - Used in a Report

Look up a value into a text box, based on a value in another report field:






Read as:
Find a record in the tblEmployees table;
with EmployeeID equal to the EmployeeID displayed in the report;
and return the Employee Name from this record

Example #2 - an unbound text box in a form:






Read this as:
Find a record in tblApplications;
which has ApplicationID = ApplicationID in the form frmApplications;
return the ApplicationType from this record

Example #3 - Combine DLookup with Format$


This returns the value of [ReportID], (which is an integer in my example) and formats it as a string consisting of 'R' followed by three digits, e.g. '3' would be displayed as 'R003'

Example 4 - Set a visual basic variable equal to a value from a table
stStringVariable = DLookup("[ColumnName]", "tblTableName", "[ColumnName2] = 'Value'")

Example: look up the Database Name from a Control table, where:

ParameterValue stores the value to be looked up;
ParameterName stores the name of the value to be looked up;
The table is called tblControl1.


stDatabaseName = DLookup("[ParameterValue]", "tblControl1", "[ParameterName] = 'DatabaseName'")
NB take care with the single quotes round the text string to be looked up - 'DatabaseName'

Example 5 - a DLookup which concatenates fields

=DLookUp([EmployeeSurname] & ' ' & [EmployeeForenames], tblEmployees, [EmployeeID] = 1234567)

If there is a record in the table with:

EmployeeID = 1234567
EmployeeSurname = Wilson
EmployeeForenames = John

the DLookup function will return:

John Wilson

Note the use of & ' ' & to insert a space between the two parts of the name.

Example #6 - DLookup in a sub-form

Similar to example #2, but you need slightly different syntax:



End of FAQ text

Back to Microsoft: Access Modules (VBA Coding) FAQ Index
Back to Microsoft: Access Modules (VBA Coding) Forum

My Archive


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close