Dlookup

[Bertrand82]

Hi,



Got one Dlookup function to work but not this one in my qryDepartment.

Need some help, please view uploaded file.

Br Bertrand

[June7]

Why DLookup - the data is already there, just include DpartmentEquals field in the query.


Department is a text datatype field. Need apostrophe delimiters:

Dlookup: DLookUp("[DepartmentEquals]","[DepartmentInformation]","[Department]='" & [Department] & "'")

A date/time field would use # character.

Better would be to join the tables in query. Domain aggregate functions can slow down queries.

[Bertrand82]

Thank you, it worked.

And if the value is not found I want the Dlookup value to return "Unknown"

What will the function be?

Thanks in advance

[June7]

Nz(DLookUp("[DepartmentEquals]","[DepartmentInformation]","[Department]='" & [Department] & "'"), "Unknown")

[Bertrand82]

HI June, Thank you.

Though I have noticed that, as you said, the dlookup has slowed down my query.

Do I join the tables by relationsships and get the same results as with the dlookup?

[June7]

I should have noted before that with a DLookup, you had the wrong table in the query - your query should have been:
SELECT Department, DLookUp("[DepartmentEquals]","[DepartmentInformation]","[Department]='" & [Department] & "'") AS Dlookup FROM Data;

A query joining tables would be:
SELECT Data.Department, DepartmentEquals FROM Data LEFT JOIN DepartmentInformation ON Data.Department = DepartmentInformation.Department;

[Bertrand82]

Thank you June, Worked perfectly!

Have a nice day.

br Bertrand