dlookup

[Jason Frazer]

I don't understand the problem with this code.
As you can see below I'm testing 3 criteria3. the way the program stands i
get a null value error during the dlookup command.
It should return a value at all times. If i use the REM lines instead the
code works great. I get a value. If you look at what each code set the
strCriteria field seem to be the same.

Here this what the field strCriteria is set to.
as program stands. strCriteria = "[ZipCode]='67530 ' And [City]='Great Bend'
And [County]='Barton'"
if use the RM lines strCriteria = "[ZipCode]='67530 ' And [City]='Great
Bend' And [County]='Barton'"

Dim strPostalCode5digit As String
strPostalCode5digit = Left(strShipPostalCode, 5)
Rem strCriteria1 = "[ZipCode]='67530 '"
Rem strCriteria2 = "[City]='Great Bend'"
Rem strCriteria3 = "[County]='Barton'"
strCriteria1 = "[ZipCode]='" & strPostalCode5digit & " " & "'"
strCriteria2 = "[City]='" & strShipCityField & "'"
strCriteria3 = "[County]='" & strCountyField & "'"
strCriteria = strCriteria1 & " And " & strCriteria2 & " And " &
strCriteria3

JurCode = DLookup(strJurField, "Table_Tax_Rate", strCriteria)
curTaxRate = DLookup(strTaxField, "Table_Tax_Rate", strCriteria)

Please help. This is driving me nuts

Jason Frazer

 

[Ted]

Hi Jason,

It's hard to say from looking at the code what the
problem may be. One thing that I noticed which seems
funny, but I assume is intentional (because it is in both
criteria statements) is the extra space after the Zip
Code. Even if you need the space though, you could use

strCriteria1 = "[ZipCode]='" & strPostalCode5digit & " '"

Rather than,

strCriteria1 = "[ZipCode]='" & strPostalCode5digit & " "
& "'"

But I don't think that would make a difference.

I would add a breakpoint in the code right at the Dlookup
statement and take a look at what your strCriteria value
is, as well as the other variables used to construct it.
Maybe you will be able to spot the problem.