Dlookup error (1 Viewer)

Snowflake68 · Sep 18, 2017

I am trying to lookup the EmpInitials from table tblEmployees but cannot get the criteria working.

On the same form I have a textbox called txtUsername which obtains the Windows username (this works ok). I want to lookup the users initials for the windows username but the code below just shows '#Error'

Code:

=DLookUp("[EmpInitials]","tblEmployees","[EmpName] =" & [Forms]![frmLogon]![txtUserName])

This must be something simple that I just cant work out. Been looking at this all morning and am going around in circles now so hope someone can spot what I have wrong.

plog · Sep 18, 2017

When you compare strings, you need to tell the computer where the string begins and ends. That means surrounding the string value by a character that tells the computer whatever is inside those characters is a string.

You can do that with double or single quotes. Since you are using double quotes already in your DLookup, you need to use single quotes. Put them around the variable like so in your criteria argument:

"[EmpName] = '" & [Forms]![frmLogon]![txtUserName] & "'"

RuralGuy · Sep 18, 2017

I'll bet it is a string value so enclose with single quotes:
=DLookUp("[EmpInitials]","tblEmployees","[EmpName] ='" & [Forms]![frmLogon]![txtUserName] & "'")

Snowflake68 · Sep 18, 2017

plog said:
When you compare strings, you need to tell the computer where the string begins and ends. That means surrounding the string value by a character that tells the computer whatever is inside those characters is a string.

You can do that with double or single quotes. Since you are using double quotes already in your DLookup, you need to use single quotes. Put them around the variable like so in your criteria argument:

"[EmpName] = '" & [Forms]![frmLogon]![txtUserName] & "'"

Thank so much. Works perfectly now.

=DLookUp("[EmpInitials]","tblEmployees","[EmpName] = '" & [txtUserName] & "'")

Pat Hartman · Sep 18, 2017

Now that the query works, you might want to consider changing it to use EmpID rather than EmpName. I don't know about your company but at mine we have a number of instances where employees have the same name. Using a DLookup() with a non-unique search argument will return the "first" result that matches.

Dlookup error (1 Viewer)

Snowflake68

Registered User.

plog

Banishment Pending

RuralGuy

AWF VIP

Snowflake68

Registered User.

Pat Hartman

Super Moderator

Similar threads

Users who are viewing this thread