DLookup

[Guest]

I'm trying to get a DLookup expression to lookup the value of 'Team Name'
from table Employees based on the value of employees in the active form. I
get #ERROR but can't figure out why.
=DLookUp("[Team Name]","[Employees]","[Team]=" & Forms!Count!Employees)

All fields are text values. Any help is appreciated.

Thank you,
MC

 

[Guest]

This might be really obvious, but I've been caught by it-- Is that field
holding the dlookup named the same as a field in the table? I preface mine
with "Txt" to avoid that. Hope it helps!

 

[Sandra Daigle]

When using text fields in the criteria Dlookup the comparison value must be
a string. Since VBA takes the first and last quote character as a string
delimiter you have to wrap the value in an extra pair of quotes. In order to
get a quote character inside of a VBA string you put 2 quote characters:

=DLookUp("[Team Name]","[Employees]","[Team]=""" & Forms!Count!Employees &
"""")

Is the form named Count? You might want to consider renaming it since Count
is a property of the Forms Collection and although forms!count and
forms.count refer to very different things, it is always a good idea to
refrain from naming fields, forms, and objects with Access keywords and
common property names.