Dlookup error

[Snowflake68]

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'

=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]

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]

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

 

[Snowflake68]

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]

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.