Null value in SQL statement

  • Thread starter Thread starter G Lam
  • Start date Start date
G

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
 
G Lam said:
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
Click to expand...

Nothing is ever "equal Null".

Use
WHERE SomeField Is Null
or
WHERE IsNull(SomeField)
 
Back
Top