simple DLookup() not working (1 Viewer)

[JJSHEP89]

So i've been fighting this for a while now and dont understand why it's not functioning. I have a multi-user database that links via ODBC to multiple backend tables from various systems. In this database i've created a simple communications area to allow for employees to document issues, things to be fixed etc. and a form that lists all the open communications in a listbox. Upon double clicking a particular entry it opens up a form that populates basic information on the communication and gives a textbox area to allow for users to type in responses, resolutions, updates, etc. and save it to the record.

everything is working fine except a simple DLookup field, each communication has a text field saving an employee ID number, i use this employee ID number to link to our employees table to retrieve the name and display the name on the pop up form allowing for the updates.

here is the actual DLookup

=DLookUp("Name","dbo_ICEp_vwEmpBasic","[dbo_ICEp_vwEmpBasic]![EmpID]= " & [txtEE_NUM])

the name field is a text string, and the employee ID fields on both tables are text strings. the txtEE_NUM is a textbox that i have set on my form.

I've tried a multitude of variations and each time either get an #Error, or #name response.

Unfortunately for a multitude of reasons i cant upload a sample DB, so hopefully i've been descriptive enough here to understand the issue.

TIA!

 

[CJ_London]

Name is a reserved word - it can create issues - see this link https://www.access-programmers.co.uk/forums/showthread.php?p=1593239#post1593239

you do not need [dbo_ICEp_vwEmpBasic]! here - and should use a dot (.) not a bang(!) if you do

"[dbo_ICEp_vwEmpBasic]![EmpID]= " & [txtEE_NUM])

if |Emp_ID is a text string then you need to use single quotes

[EmpID]= '" & [txtEE_NUM] & "'"

generally speaking it is a bad idea to store numbers as text

 

[JJSHEP89]

Name is a reserved word - it can create issues - see this link https://www.access-programmers.co.uk/forums/showthread.php?p=1593239#post1593239

you do not need [dbo_ICEp_vwEmpBasic]! here - and should use a dot (.) not a bang(!) if you do

"[dbo_ICEp_vwEmpBasic]![EmpID]= " & [txtEE_NUM])

if |Emp_ID is a text string then you need to use single quotes

[EmpID]= '" & [txtEE_NUM] & "'"

generally speaking it is a bad idea to store numbers as text

I did not know name was a reserve word, thanks for that! Unfortunately the empID field has both text and numbers, it was that way before i even started here and there's nothing i can do to change it. I'll roll with it either way.

changed it up to this and it works just fine now.

=DLookUp("EmpName","dbo_ICEp_vwEmpBasic","[EmpID]= '" & [txtEE_NUM] & "'")