How to Handle Null values in Sql update statment

[tonialbrown]

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.Colu mn(1) & "'," & _
"[delegation].[other] = '" & lstDelFrom.Colu mn(2) & "'," & _
"[delegation].[name] = '" & lstDelFrom.Colu mn(3) & "'," & _
"[delegation].[title] = '" & lstDelFrom.Colu mn(4) & "'," & _
"[delegation].[section] = '" & lstDelFrom.Colu mn(5) & "'" & _
"Where ([delegation].[id] = '" & [Forms]![frmdelnewproper ties]![id] & "')"

Is there a way to handle this situation. Thanks in advance.

[Delerna]

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

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

otherwise use

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

[Minion]

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:
[code=sql]
sql = "update delegation " & _
"Set [delegation].[seg] = '" & Nz(lstDelFrom.C olumn(1), "NA") & "'," & _
"[delegation].[other] = '" & Nz(lstDelFrom.C olumn(2), "NA") & "'," & _
"[delegation].[name] = '" & Nz(lstDelFrom.C olumn(3), "NA") & "'," & _
"[delegation].[title] = '" & Nz(lstDelFrom.C olumn(4), "NA") & "'," & _
"[delegation].[section] = '" & Nz(lstDelFrom.C olumn(5), "NA") & "'" & _
"Where ([delegation].[id] = '" & [Forms]![frmdelnewproper ties]![id] & "')"
[/code]

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.Colu mn(1) & "'," & _
"[delegation].[other] = '" & lstDelFrom.Colu mn(2) & "'," & _
"[delegation].[name] = '" & lstDelFrom.Colu mn(3) & "'," & _
"[delegation].[title] = '" & lstDelFrom.Colu mn(4) & "'," & _
"[delegation].[section] = '" & lstDelFrom.Colu mn(5) & "'" & _
"Where ([delegation].[id] = '" & [Forms]![frmdelnewproper ties]![id] & "')"

Is there a way to handle this situation. Thanks in advance.

[tonialbrown]

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:
[code=sql]
sql = "update delegation " & _
"Set [delegation].[seg] = '" & Nz(lstDelFrom.C olumn(1), "NA") & "'," & _
"[delegation].[other] = '" & Nz(lstDelFrom.C olumn(2), "NA") & "'," & _
"[delegation].[name] = '" & Nz(lstDelFrom.C olumn(3), "NA") & "'," & _
"[delegation].[title] = '" & Nz(lstDelFrom.C olumn(4), "NA") & "'," & _
"[delegation].[section] = '" & Nz(lstDelFrom.C olumn(5), "NA") & "'" & _
"Where ([delegation].[id] = '" & [Forms]![frmdelnewproper ties]![id] & "')"
[/code]

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.

[tonialbrown]

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

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

otherwise use

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

Thanks I will have a look and see if I can incorporate in my code.

[tonialbrown]

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

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

otherwise use

field=iif(field 1 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.Colu mn(3))

as the value ended up being a blank rather than a null value.