cannot update null value

S

Sandspur

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] & "));"
 
S

scubadiver

How can you update something that doesn't exist in the first place.

Try using "Is Null"
 
J

John Spencer

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
..
 
Top