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

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

P: 19
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
Share this Question
Share on Google+
3 Replies


JustJim
Expert 100+
P: 407
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

P: 19
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
Expert 100+
P: 407
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

Post your reply

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