Access DLookup and lookup multiple criteria

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

access_login_form_02

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.

access_login_form

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

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist
- Microsoft Specialist
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *