Null values being generated from list box - can't understand why

[tonialbrown]

I am having some problems with an update statement. The problem is the data comes from a list box lstDelFrom that the user selects for the required record that they are copying the data from. This list box is generated from a query that selects information from the delegation table only.See below code.

SQL = "update delegation " & _
"Set [delegation].[seg] = '" & IIf(lstDelFrom. Column(1) = "", " ", lstDelFrom.Colu mn(1)) & "'," & _
"[delegation].[other] = '" & IIf(lstDelFrom. Column(2) = "", " ", lstDelFrom.Colu mn(2)) & "'," & _
"[delegation].[name] = '" & IIf(lstDelFrom. Column(3) = "", " ", lstDelFrom.Colu mn(3)) & "'," & _
"[delegation].[title] = '" & IIf(lstDelFrom. Column(4) = "", " ", lstDelFrom.Colu mn(4)) & "'," & _
"[delegation].[section] = '" & IIf(lstDelFrom. Column(5) = "", " ", lstDelFrom.Colu mn(5)) & "'," & _
"[delegation].[keywords] = '" & Nz(lstDelFrom.C olumn(6), "NA") & "'," & _
"[delegation].[adopted] = '" & Nz(lstDelFrom.C olumn(8), "NA") & "'" & _
"Where ([delegation].[id] = '" & [Forms]![frmdelnewproper ties]![id] & "')"


The query that generates the list box lstdelfrom is:
SELECT delegation.id, delegation.seg, delegation.othe r, delegation.name , delegation.titl e, delegation.sect ion, delegation.keyw ords, delegation.dept , delegation.adop ted, delegation.[on], delegation.last rev, delegation.next rev, delegation.dele ffdate, delegation.dele nddate, delegation.arch ive
FROM delegation
WHERE (((delegation.a rchive)<>Yes));


What is happening is for some reason column(6) & Column(8) return Null values regardless of what data is in the the table. If I run the query I can see the data & if I do a select statement of the table I can see the data.

Columns 6 & 8 are text fields.

Could anyone give me any information as to why this could be happening.

Thanks in advance for any suggestions.

[JustJim]

I am having some problems with an update statement. The problem is the data comes from a list box lstDelFrom that the user selects for the required record that they are copying the data from. This list box is generated from a query that selects information from the delegation table only.See below code.

SQL = "update delegation " & _
"Set [delegation].[seg] = '" & IIf(lstDelFrom. Column(1) = "", " ", lstDelFrom.Colu mn(1)) & "'," & _
"[delegation].[other] = '" & IIf(lstDelFrom. Column(2) = "", " ", lstDelFrom.Colu mn(2)) & "'," & _
"[delegation].[name] = '" & IIf(lstDelFrom. Column(3) = "", " ", lstDelFrom.Colu mn(3)) & "'," & _
"[delegation].[title] = '" & IIf(lstDelFrom. Column(4) = "", " ", lstDelFrom.Colu mn(4)) & "'," & _
"[delegation].[section] = '" & IIf(lstDelFrom. Column(5) = "", " ", lstDelFrom.Colu mn(5)) & "'," & _
"[delegation].[keywords] = '" & Nz(lstDelFrom.C olumn(6), "NA") & "'," & _
"[delegation].[adopted] = '" & Nz(lstDelFrom.C olumn(8), "NA") & "'" & _
"Where ([delegation].[id] = '" & [Forms]![frmdelnewproper ties]![id] & "')"


The query that generates the list box lstdelfrom is:
SELECT delegation.id, delegation.seg, delegation.othe r, delegation.name , delegation.titl e, delegation.sect ion, delegation.keyw ords, delegation.dept , delegation.adop ted, delegation.[on], delegation.last rev, delegation.next rev, delegation.dele ffdate, delegation.dele nddate, delegation.arch ive
FROM delegation
WHERE (((delegation.a rchive)<>Yes));


What is happening is for some reason column(6) & Column(8) return Null values regardless of what data is in the the table. If I run the query I can see the data & if I do a select statement of the table I can see the data.

Columns 6 & 8 are text fields.

Could anyone give me any information as to why this could be happening.

Thanks in advance for any suggestions.

The Nz(variant, [valueifnull]) Function is designed for datatypes of Variant only. You may have to do some pre-checking of the contents and assign a value to a string variable to insert into your SQL string (or use the dreaded Iif funcion)

Jim

[tonialbrown]

The Nz(variant, [valueifnull]) Function is designed for datatypes of Variant only. You may have to do some pre-checking of the contents and assign a value to a string variable to insert into your SQL string (or use the dreaded Iif funcion)

Jim

Thanks Jim for clarifying this. I have actually changed my Nz statement to the get the values from a recordset rather than getting the values from a list box and it seems to work with the following:

"[delegation].[keywords] = '" & Nz(rst.Fields(6 ).Value, " ") & "'," & _
"[delegation].[adopted] = '" & Nz(rst.Fields(8 ).Value, " ") & "'," & _

So I am assuming these are variants when they are stored in the recordset rst

[JustJim]

Thanks Jim for clarifying this. I have actually changed my Nz statement to the get the values from a recordset rather than getting the values from a list box and it seems to work with the following:

"[delegation].[keywords] = '" & Nz(rst.Fields(6 ).Value, " ") & "'," & _
"[delegation].[adopted] = '" & Nz(rst.Fields(8 ).Value, " ") & "'," & _

So I am assuming these are variants when they are stored in the recordset rst

Actually Nz does tend to work on non-variant datatypes as well, but then sometimes it don't!

As long as you got it working....

Jim