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.Column(1)) & "'," & _
"[delegation].[other] = '" & IIf(lstDelFrom.Column(2) = "", " ", lstDelFrom.Column(2)) & "'," & _
"[delegation].[name] = '" & IIf(lstDelFrom.Column(3) = "", " ", lstDelFrom.Column(3)) & "'," & _
"[delegation].[title] = '" & IIf(lstDelFrom.Column(4) = "", " ", lstDelFrom.Column(4)) & "'," & _
"[delegation].[section] = '" & IIf(lstDelFrom.Column(5) = "", " ", lstDelFrom.Column(5)) & "'," & _
"[delegation].[keywords] = '" & Nz(lstDelFrom.Column(6), "NA") & "'," & _
"[delegation].[adopted] = '" & Nz(lstDelFrom.Column(8), "NA") & "'" & _
"Where ([delegation].[id] = '" & [Forms]![frmdelnewproperties]![id] & "')"


The query that generates the list box lstdelfrom is:
SELECT delegation.id, delegation.seg, delegation.other, delegation.name, delegation.title, delegation.section, delegation.keywords, delegation.dept, delegation.adopted, delegation.[on], delegation.lastrev, delegation.nextrev, delegation.deleffdate, delegation.delenddate, delegation.archive
FROM delegation
WHERE (((delegation.archive)<>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.

Jan 24 '08 #1

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.Column(1)) & "'," & _
"[delegation].[other] = '" & IIf(lstDelFrom.Column(2) = "", " ", lstDelFrom.Column(2)) & "'," & _
"[delegation].[name] = '" & IIf(lstDelFrom.Column(3) = "", " ", lstDelFrom.Column(3)) & "'," & _
"[delegation].[title] = '" & IIf(lstDelFrom.Column(4) = "", " ", lstDelFrom.Column(4)) & "'," & _
"[delegation].[section] = '" & IIf(lstDelFrom.Column(5) = "", " ", lstDelFrom.Column(5)) & "'," & _
"[delegation].[keywords] = '" & Nz(lstDelFrom.Column(6), "NA") & "'," & _
"[delegation].[adopted] = '" & Nz(lstDelFrom.Column(8), "NA") & "'" & _
"Where ([delegation].[id] = '" & [Forms]![frmdelnewproperties]![id] & "')"


The query that generates the list box lstdelfrom is:
SELECT delegation.id, delegation.seg, delegation.other, delegation.name, delegation.title, delegation.section, delegation.keywords, delegation.dept, delegation.adopted, delegation.[on], delegation.lastrev, delegation.nextrev, delegation.deleffdate, delegation.delenddate, delegation.archive
FROM delegation
WHERE (((delegation.archive)<>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

Jan 30 '08 #2

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

Jan 30 '08 #3

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

Jan 31 '08 #4