cannot update null value

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

if Me![CheckSalesperson] has a value and Me![CheckSalesTaxNumber] has a
value the update will occur.

if either value is null then nothing is updated.

UPDATE [ar customer] SET [ar customer].SalesPerson = '" &
Me![CheckSalesperson] & "', [ar customer].SalesTaxNumber = '" &
Me![CheckSalesTaxNumber] & "' WHERE ((([ar customer].CompanyID)= " &
Me![CompanyID] & "));"
 
How can you update something that doesn't exist in the first place.

Try using "Is Null"
 
You need to handle the null values. You can try something like the
following UNTESTED code

Dim strPerson as String
Dim strSalesTax as String

If IsNull(Me.CheckSalesPerson) then
strPerson = " NULL "
Else
strPerson = """" & Me.CheckSalesPerson & """"
End If

If IsNull(Me.CheckSalesTaxNumber) then
strSalesTax = " NULL "
Else
strSalesTax = """" & Me.CheckSalesTaxNumber& """"
End If


StrSQL = "UPDATE [ar customer] " & _
" SET [ar customer].SalesPerson = " & strPerson & " , " & _
" [ar customer].SalesTaxNumber = "& strSalesTax & _
" WHERE [ar customer].CompanyID= " & Me![CompanyID] & ";"

Your other option is to change the fields to allow zero length strings or to
load dummy values into the fields is the controls are null.

"UPDATE [ar customer] SET [ar customer].SalesPerson = '" &
NZ(Me![CheckSalesperson],"UNKNOWN") & "', [ar customer].SalesTaxNumber = '"
&
NZ(Me![CheckSalesTaxNumber],"UNKNOWN") & "' WHERE ((([ar
customer].CompanyID)= " &
Me![CompanyID] & "));"


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Back
Top