This Access tutorial explains how to use DLookup Function to lookup value in Query or Table, and demonstrate how to DLookup multiple criteria.
You may also want to read:
Access user login Form and verify password
Access DLookup Function and lookup multiple criteria
DLookup Function is very similar to Vlookup of Excel, the purpose is to look up the first matched value of particular Field from another table.
To describe more precisely, DLookup is same as a SQL Select statement, but break a SQL statement into pieces as DLookup arguments.
DLookup is extremely useful in Access, because it can be used in anywhere, like Report, Form, Expression, VBA, but the best part is that the lookup table can be completely unrelated to Record Source, which means you can freely lookup from any Table.
Syntax of Access DLookup Function
DLookup("FieldName" , "TableName" , "Criteria = n")
which is same as
Select FieldName From TableName Where Criteria=n
| Argument | Description |
| FieldName | The Field that contains the lookup value. Use square brackets [ ] if FieldName contains space |
| TableName | The lookup Table name or Query name |
| Criteria | Optional. If not given, a random result will returnLookup value is a number: “Criteria=n”Lookup value is a string: “Criteria=’n'”
Lookup value is a date: “Criteria=#date#” |
DLookup returns Null is no matched value is returned.
Example of Access DLookup Function – more than 1 criteria
| Formula | Value | Explanation |
| DLookUp(“Login_Pwd”,”login_tbl”,”[Empl ID]=’001′”) | 1 | |
| Dlookup(“[Empl ID]”,”login_tbl”,”User_Type=’User'”) | 2 | If more than 1 result, only the first is returned |
| Dlookup(“[Empl ID]”,”login_tbl”,”User_Type=’User’ AND Login_Pwd=’2′”) | 2 | Use multiple criteria |
| Dlookup(“[Empl ID]”,”login_tbl”,”User_Type='”&Forms![Form1]!tb1&”‘”) | Dlookup criteria using Control of another Form |
Use DLookup to verify password in Login Form
Below is an example using Text Box of User ID (tb_ID) and Password (tb_pwd) in DLookup to check if password is correct.
Me.tb_pwd.Value = DLookup("[Login_Pwd]", "login_tbl", "[Empl ID]='" & Me.tb_ID.Value & "'")
If you want to read more about creating Access Login Form, click here.
Outbound References
https://support.office.com/en-us/article/DLookup-Function-8896CB03-E31F-45D1-86DB-BED10DCA5937
