INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS
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.
|
Microsoft: Access Modules (VBA Coding) FAQ Share
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
