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
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.
Partner With Us!
"Best Of Breed" Forums Add Stickiness To Your Site
(Download This Button Today!)
Feedback
"...Congratulations for your great site. This site helped me more than university..."
Geography
Where in the world do Tek-Tips members come from?
|
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.
CODEstDatabaseName = 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:
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
|
|
 |
|
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
