Null value in SQL statement

[G Lam]

Hi, I wanted to records where one of the fields had null value, but got
"Datatype mismatch in criteria expression" error, or "Misuse of Null". I
checked there was
nothing wrong with the data type. Here are my code:
Dim rptSONbr As String, VarCusIDAs String
rptSONbr = Forms!frmPack!fsfrPack!SubOrdNbr
VarCusID = Forms!frmPack!fsfrPack!CustID
DoCmd.Close acForm, "frmPack"
Dim dbDel As Database
Set dbDel = DBEngine(0)(0)
dbDel.Execute "Delete * from tblSubPack where tblSubPack.SubOrdNbr=" _
& rptSONbr & " AND tblSubPack.CustID=""" & VarCusID & """"
I substitued the VarCusID with IsNull, "" or Null, but none worked.
How can I make this work?
Thank you in advance.
Gary

 

[Rick Brandt]

Hi, I wanted to records where one of the fields had null value, but got
"Datatype mismatch in criteria expression" error, or "Misuse of Null". I
checked there was
nothing wrong with the data type. Here are my code:
Dim rptSONbr As String, VarCusIDAs String
rptSONbr = Forms!frmPack!fsfrPack!SubOrdNbr
VarCusID = Forms!frmPack!fsfrPack!CustID
DoCmd.Close acForm, "frmPack"
Dim dbDel As Database
Set dbDel = DBEngine(0)(0)
dbDel.Execute "Delete * from tblSubPack where tblSubPack.SubOrdNbr=" _
& rptSONbr & " AND tblSubPack.CustID=""" & VarCusID & """"
I substitued the VarCusID with IsNull, "" or Null, but none worked.
How can I make this work?
Thank you in advance.
Gary

Nothing is ever "equal Null".

Use
WHERE SomeField Is Null
or
WHERE IsNull(SomeField)

 

[Wayne Morgan]

tblSubPack.CustID=""" & VarCusID & """"

tblSubPack.CustID Is Null