INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS
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
- 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.
Partner With Us!
"Best Of Breed" Forums Add Stickiness To Your Site
(Download This Button Today!)
"...it was ingeniously designed and all those clicks were for my own good... and that was even before I got my speedy and useful answer to my tekkie question that I eventually posted..."
Where in the world do Tek-Tips members come from?
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:
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.
NB take care with the single quotes round the text string to be looked up - 'DatabaseName'
stDatabaseName = DLookup("[ParameterValue]", "tblControl1", "[ParameterName] = '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:
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
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:
- Talk To Other Members
- Notification Of Responses To Questions
- Favorite Forums One Click Access
- Keyword Search Of All Posts, And More...
Register now while it's still free!
Already a member? Close this window and log in.
Join Us Close