Dlookup driving me crazy

[NJMike64]

All,

It has been a couple of years since I've done db design, but getting back into it for personal needs. I can't recall ever having such a hard time with Dlookup. Here is the code;

Dim TestE As String
TestE = DLookup("email", "Employee", "efullnamerev = '" & Forms![frmofficeadd]![PrimName] & "'")
MsgBox TestE

In the table related to frmofficeadd, the PrimName is a combo/lookup back to the employee table efullnamerev. When you go into the form you select the name and after update, it should run the above code and set TestE to the email address that is stored in the employee table related to the name.

Right now, the code keeps returning null.

If I set the last bit to "efullnamerev = Smith, John'" It returns the email address associated with this name from the employee table.

Its late and I am sure I am missing something minor here.

Thanks
Mike

[ItsMe]

I don't use lookup fields in tables but, the last time I checked the value stored in lookup fields it is the index of the lookup that is stored, not the literal text.

[June7]

Yes, PrimName field probably actually holds the employee ID, not name. Assuming the ID is number type (autonumber field?):

DLookup("email", "Employee", "ID = " & Forms![frmofficeadd]![PrimName])

I also never build lookups in tables, especially lookups with alias, just because of this confusion. I prefer to see the actual value, not the alias.

[NJMike64]

Thanks to both of you. You called it right on that one. I thought that I was saving a step by doing the lookup in the table, but can see how that will just cause more confusion later. As you both said, it was storing the autonumber and not the literal value.

Thanks again.

Mike