Dlookup using multiple criteria

[larochy]

Hi, I'm using a Dlookup with multiple criteria and it's not working. The
first part of the criteria refers to a field on my form and the second part
of the criteria is just text. The first part works but the second part of
the lookup isn't working. Please advise.

Me.Rate = DLookup("Rate", "tblStandardRates", "CompanyCode = " &
Me.CompanyCode And "Role = Application Engineer")

 

[Allen Browne]

You need quotes arount the literal text in the 3rd argument, and the And
needs to be inside the quotes too.

It's probably easiest if you create the criteria as a string:

Dim strWhere As String
strWhere = "(CompanyCode = " & Me.CompanyCode & ") And (Role = ""Application
Engineer"")"
Me.Rate = DLookup("Rate", "tblStandardRates", strWhere)

If those quotes don't make sense, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

Also, if CompanyCode is null (e.g. at a new record?), the string will be
malformed.

 

[tina]

you've almost got it. try

Me.Rate = DLookup("Rate", "tblStandardRates", "CompanyCode = " &
Nz(Me.CompanyCode, 0) & " And Role = 'Application Engineer'")

the above goes all on one line, regardless of line-wrap in this post.

hth