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:
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 ________________________________________
Read this as: Find a record in tblApplications; which has ApplicationID = ApplicationID in the form frmApplications; return the ApplicationType from this record ________________________________________
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.
Talk To Other Members