update query null issue

[John]

The following sql statement works fine if the 3 txtboxes (txt...) have
info in them. If any one of them does not have info in them then a
syntax error occurs. I am having difficulty in resolving this if
there is no info in any of them. Can any one help out on this?
Thanks.

sql = "UPDATE BuiltList " & _
"SET StandardWarrantyLength = " &
txtContractWarrantyLength & ", " & _
"MaxWarrantyLength = " &
txtMaxWarrantyLength & ", " & _
"Reason = '" & txtReason & "' " & _
"WHERE PartNumber = '" & Forms!
xMultipleDeviceWarrantyInformationUpdate!
lstMultipleDeviceWarrantyUpdateList.column(0, intCount) & "' AND
LotNumber = '" & Forms!xMultipleDeviceWarrantyInformationUpdate!
lstMultipleDeviceWarrantyUpdateList.column(1, intCount) & "' "

 

[John W. Vinson]

The following sql statement works fine if the 3 txtboxes (txt...) have
info in them. If any one of them does not have info in them then a
syntax error occurs. I am having difficulty in resolving this if
there is no info in any of them. Can any one help out on this?
Thanks.

sql = "UPDATE BuiltList " & _
"SET StandardWarrantyLength = " &
txtContractWarrantyLength & ", " & _
"MaxWarrantyLength = " &
txtMaxWarrantyLength & ", " & _
"Reason = '" & txtReason & "' " & _
"WHERE PartNumber = '" & Forms!
xMultipleDeviceWarrantyInformationUpdate!
lstMultipleDeviceWarrantyUpdateList.column(0, intCount) & "' AND
LotNumber = '" & Forms!xMultipleDeviceWarrantyInformationUpdate!
lstMultipleDeviceWarrantyUpdateList.column(1, intCount) & "' "

What do you want to happen if the form control is in fact empty? Cancel the
update and warn the user, put in a default value, insert a NULL value, or
what?
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[John]

What do you want to happen if the form control is in fact empty? Cancel the
update and warn the user, put in a default value, insert a NULL value, or
what?
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com- Hide quoted text -

- Show quoted text -

I thought about that just after I sent the original post. I tried,
and it worked, the Nz in front of the txt. If the txt was empty then
the value that is already listed would remain. Is this the proper way
to handle this?
John

 

[John W. Vinson]

I thought about that just after I sent the original post. I tried,
and it worked, the Nz in front of the txt. If the txt was empty then
the value that is already listed would remain. Is this the proper way
to handle this?

Well, of course it's "proper" if it does what you want it to do... which you
would know better than I!

I think you're ok. You can use the optional second parameter to NZ() to
specify the value if it's not in the form control, e.g.

NZ(txtReason, "Reason not specified")

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com