Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • 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!

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

Donate Today!

Do you enjoy these
technical forums?
Donate Today! Click Here

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.

Microsoft: Access Modules (VBA Coding) FAQ

Functions

DLookup Function Examples
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:

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
________________________________________

Example #3 - Combine DLookup with Format$

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

Example 4 - Set a visual basic variable equal to a value from a table
[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

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