dlookup multiple criteria

[Bonnie]

Hi,

Having a little problem with syntax in a DLOOKUP

Here's what I have:

Me!CkSvcDiv = DLookup("DivZone", "MasterZips", "Zipcode = '" & Me.JobZip &
"'")

I would like to add - 'and CITY = Me.JobCity and COUNTY = Me.JobCounty.

Can anyone help?

Thanks in advance.

Bonnie

 

[Yanick]

Try this:

Me!CkSvcDiv = DLookup("[DivZone]", "MasterZips", "Zipcode = '" & Me.JobZip &
"' And CITY ='" & Me.JobCity & "' And COUNTY = '" & Me.JobCounty & "'")

Should work!

 

[Bonnie]

Thanks the ticket - thanks Yanick

 

[John W. Vinson]

Hi,

Having a little problem with syntax in a DLOOKUP

Here's what I have:

Me!CkSvcDiv = DLookup("DivZone", "MasterZips", "Zipcode = '" & Me.JobZip &
"'")

I would like to add - 'and CITY = Me.JobCity and COUNTY = Me.JobCounty.

The third argument to any of the Domain functions is a text string which
evaluates to a valid SQL WHERE clause (without the word WHERE). One handy way
to see how this should look is to create a Query in the grid which returns the
desired records, and open the query in SQL view. Find the WHERE clause and
that will give you the syntax.

In this case you have three text fields, each of which needs to be delimited
by quotemarks; since some city and county names might have apostrophes, it
would be best to use two consecutive doublequotes which will translate to a
doublequote as a delimiter:

DLookup("DivZone", "MasterZips", "Zipcode = '" & Me.JobZip & "' and CITY =
""" & Me.JobCity & """ and COUNTY = """ & Me.JobCounty & """")

This will evaluate to something like

Zipcode = '83660' and CITY = "Parma" and COUNTY = "Canyon"


John W. Vinson [MVP]