Error message: Invalid use of Null

[Jason Frazer]

When i run this code I get the follow error message "Invalid use of Null"
I know the error is comming from the dlookup command.
I can't seem to figure out why.
Here are the Locals when i run the code up to when it error:

: strCriteria : "ZipCode='67063 ' And City='Hillsboro' And
County='Marion'" : String
: strTaxField : "InsideCityLimitsTaxRate" : String
: strJurField : "InsideCityLimitsJurisdictionCode" : String
: curTaxRate : 0 : Currency
: cosTaxRate : 6.3 : Currency
: chkInsideCityLimits : -1 : Variant/Integer
: strShipCityField : "Hillsboro" : String
: strCountyField : "Marion" : String
: strShipPostalCode : "67063-" : String
: strCriteria1 : "ZipCode='67063 '" : String
: strCriteria2 : "City='Hillsboro'" : String
: strCriteria3 : "County='Marion'" : String
: PostalCode5digit : 67063 : Single

The feilds for the critera in the table "Table_Tax_Rate",
[City] ="Hillsboro", ZipCode ="67063 ", County= "Marion"
all of these data type is set to text.
The code is below.

Thanks for your time and help

Jason Frazer


Option Compare Database

Private Sub Command69_Click()
On Error GoTo Err_Command69_Click
Dim strCriteria As String
Dim strTaxField As String
Dim strJurField As String
Dim JurCode As String
Dim Cnty As String
Dim curTaxRate As Currency
Dim cosTaxRate As Currency
Dim chkInsideCityLimits As Variant
Dim strShipCityField As String
Dim strCountyField As String
Dim strShipPostalCode As String
Dim strCriteria1 As String
Dim strCriteria2 As String
Dim strCriteria3 As String

Rem DoCmd.RunCommand acCmdSaveRecord
cosTaxRate = [Customer Sales Tax Rate]
strShipCityField = [Ship City]
strCountyField = [County]
strShipPostalCode = [Ship Postal Code]
chkInsideCityLimits = [InsideCityLimits]
If chkInsideCityLimits Then
strTaxField = "InsideCityLimitsTaxRate"
strJurField = "InsideCityLimitsJurisdictionCode"
Else
strTaxField = "OutsideCityLimitsTaxRate"
strJurField = "OutsideCityLimitsJurisdictionCode"
End If

Dim PostalCode5digit As Single
PostalCode5digit = Left(strShipPostalCode, 5)
strCriteria1 = "ZipCode='" & PostalCode5digit & " " & "'"
strCriteria2 = "City='" & [Ship City] & "'"
strCriteria3 = "County='" & [County] & "'"
strCriteria = strCriteria1 & " And " & strCriteria2 & " And " &
strCriteria3

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

If cosTaxRate <> curTaxRate Then
MsgBox "Customer Tax does not equal State assigned tax CLARIFICATION
NEEDED!"
End If

[Sales Tax Rate] = curTaxRate
[JurisdictionCode] = JurCode

Me.Refresh
MsgBox "Tax Information was updated."

Exit_Command69_Click:
Exit Sub

Err_Command69_Click:
MsgBox Err.DESCRIPTION
Resume Exit_Command69_Click

End Sub

 

[Jonathan Parminter]

-----Original Message-----
When i run this code I get the follow error message "Invalid use of Null"
I know the error is comming from the dlookup command.
I can't seem to figure out why.
Here are the Locals when i run the code up to when it error:

Dim JurCode As String

Dim curTaxRate As Currency

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

strCriteria)

Hi Jason,
you are storing the result of your dlookup in currency and
string data types. Neither of them can accept null values
when the dlookup field has no value.


either use variant data type, that can accept null values,
or use the nz function, to return an acceptable value. The
following example will return 0 instead of a null.

curTaxRate = nz(DLookup(strTaxField, "Table_Tax_Rate",
strCriteria),0)

Luck
Jonathan