Dlookup using multiple criteria

  • Thread starter Thread starter larochy
  • Start date Start date
L

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")
 
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.
 
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
 
Back
Top