By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,665 Members | 1,382 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,665 IT Pros & Developers. It's quick & easy.

How to Handle Null values in Sql update statment

P: 19
I have some code that updates a record's fields based on a selection from a list box (lstDelFrom). The user selects the record from the list box & it copies it into the fields [seg], [other], [name] etc.

My problem is that the code only works if all the fields that are being copied from have information in them. It does not work when some of the fields are null such as [seg] & [other] which is often the case.

sql = "update delegation " & _
"Set [delegation].[seg] = '" & lstDelFrom.Column(1) & "'," & _
"[delegation].[other] = '" & lstDelFrom.Column(2) & "'," & _
"[delegation].[name] = '" & lstDelFrom.Column(3) & "'," & _
"[delegation].[title] = '" & lstDelFrom.Column(4) & "'," & _
"[delegation].[section] = '" & lstDelFrom.Column(5) & "'" & _
"Where ([delegation].[id] = '" & [Forms]![frmdelnewproperties]![id] & "')"

Is there a way to handle this situation. Thanks in advance.
Jan 17 '08 #1
Share this Question
Share on Google+
5 Replies


Delerna
Expert 100+
P: 1,134
cant remember if the isNull() fuction works in access or not
if so then use

field= isnull(field1,"") & isnul(field2,"")........

otherwise use

field=iif(field1 is null,"",field1) & iif(field2 is null,"",field2 .........
Jan 18 '08 #2

Minion
Expert 100+
P: 108
I think the function you're looking for here is the Nz (no zeror) function. I'm not sure types of values you're passing so for right now I'll use NA as a place holder, if you want another value in place of a null just change the NA to the appropriate value.

The change to the code would look like:
Expand|Select|Wrap|Line Numbers
  1. sql = "update delegation " & _
  2.     "Set [delegation].[seg] = '" & Nz(lstDelFrom.Column(1), "NA") & "'," & _
  3.      "[delegation].[other] = '" & Nz(lstDelFrom.Column(2), "NA") & "'," & _
  4.      "[delegation].[name] = '" & Nz(lstDelFrom.Column(3), "NA") & "'," & _
  5.      "[delegation].[title] = '" & Nz(lstDelFrom.Column(4), "NA") & "'," & _
  6.      "[delegation].[section] = '" & Nz(lstDelFrom.Column(5), "NA") & "'" & _
  7.      "Where ([delegation].[id] = '" & [Forms]![frmdelnewproperties]![id] & "')"
  8.  
Hope this helps.

- Minion -


I have some code that updates a record's fields based on a selection from a list box (lstDelFrom). The user selects the record from the list box & it copies it into the fields [seg], [other], [name] etc.

My problem is that the code only works if all the fields that are being copied from have information in them. It does not work when some of the fields are null such as [seg] & [other] which is often the case.

sql = "update delegation " & _
"Set [delegation].[seg] = '" & lstDelFrom.Column(1) & "'," & _
"[delegation].[other] = '" & lstDelFrom.Column(2) & "'," & _
"[delegation].[name] = '" & lstDelFrom.Column(3) & "'," & _
"[delegation].[title] = '" & lstDelFrom.Column(4) & "'," & _
"[delegation].[section] = '" & lstDelFrom.Column(5) & "'" & _
"Where ([delegation].[id] = '" & [Forms]![frmdelnewproperties]![id] & "')"

Is there a way to handle this situation. Thanks in advance.
Jan 18 '08 #3

P: 19
I think the function you're looking for here is the Nz (no zeror) function. I'm not sure types of values you're passing so for right now I'll use NA as a place holder, if you want another value in place of a null just change the NA to the appropriate value.

The change to the code would look like:
Expand|Select|Wrap|Line Numbers
  1. sql = "update delegation " & _
  2.     "Set [delegation].[seg] = '" & Nz(lstDelFrom.Column(1), "NA") & "'," & _
  3.      "[delegation].[other] = '" & Nz(lstDelFrom.Column(2), "NA") & "'," & _
  4.      "[delegation].[name] = '" & Nz(lstDelFrom.Column(3), "NA") & "'," & _
  5.      "[delegation].[title] = '" & Nz(lstDelFrom.Column(4), "NA") & "'," & _
  6.      "[delegation].[section] = '" & Nz(lstDelFrom.Column(5), "NA") & "'" & _
  7.      "Where ([delegation].[id] = '" & [Forms]![frmdelnewproperties]![id] & "')"
  8.  
Hope this helps.

- Minion -

Thanks for your response, I did try the above but was not able to get it to work. The fields are Text fields but I am wondering if the data is actually a NULL value or not. What it is, is a field where no data entry has occurred against some of the items. In all cases there is something entered in title and usually name.

I am using a List box lstDelFrom which queries a table to get the information. Perhaps the problem occurs at the query level to create the list box or could anyone see another reason.
Jan 21 '08 #4

P: 19
cant remember if the isNull() fuction works in access or not
if so then use

field= isnull(field1,"") & isnul(field2,"")........

otherwise use

field=iif(field1 is null,"",field1) & iif(field2 is null,"",field2 .........
Thanks I will have a look and see if I can incorporate in my code.
Jan 21 '08 #5

P: 19
cant remember if the isNull() fuction works in access or not
if so then use

field= isnull(field1,"") & isnul(field2,"")........

otherwise use

field=iif(field1 is null,"",field1) & iif(field2 is null,"",field2 .........

Thanks for the replies and pointing me in the right direction, the following ended up working:

IIf(lstDelFrom.Column(3) = "", "NA", lstDelFrom.Column(3))

as the value ended up being a blank rather than a null value.
Jan 24 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.