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

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

Access DLookup and lookup multiple criteria