Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

DLookup Function Examples

Functions

DLookup Function Examples

by  BobStubbs  Posted    (Edited  )
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:
Code: 
=DLookUp("[FieldToLookup]","tblTableName","[KeyField]=Reports!rptReportName![KeyField]")

Example:
Code: 
=DLookUp("[EmployeeName]","tblEmployees","[EmployeeID]=reports!rptEmployeeList![EmployeeID]")

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:

Code: 
=DLookUp("[FieldToLookup]","tblTableName","[KeyField]=forms!frmMyFormName![KeyField]")

Example:
Code: 
=DLookUp("[ApplicationType]","tblApplications","[ApplicationID]=forms!frmApplications![ApplicationID]")

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

[b]Example #3 - Combine DLookup with Format$[/b]

=Format$(DLookUp("[ReportID]","tblReportNames","[ReportName]=txtReportName"),"\R000")

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'
________________________________________

[b]Example 4 - Set a visual basic variable equal to a value from a table[/b]
[code]
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.
Code: 
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:
Code: 
=DLookUp("[FieldToLookup]","tblTableName","[FieldName]=forms!frmMainFormName!subfrmSubFormName![FieldName]")

End of FAQ text
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top