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.
3 1499
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
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
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
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Bodza Bodza |
last post by:
I'm having an argument with an incumbent self-taught programmer that
it is OK to use null foreign keys in database design.
My take is the whole point of a foreign key is that it's not supposed...
|
by: Python_it |
last post by:
Python 2.4
MySQL-python.exe-1.2.0.win32-py2.4.zip
How can I insert a NULL value in a table (MySQL-database).
I can't set a var to NULL? Or is there a other possibility?
My var must be variable...
|
by: Kenny Mullican |
last post by:
I am using complex types in order to support serialization/deserialization
of floating point numbers, since floating points can't be null.
I've seen how to suppress attributes that are "not...
|
by: Markus Eßmayr |
last post by:
Hello,
I'd like to consume a WebService, which returns an array of objects which
include several members of type System.String, System.Decimal and
System.DateTime.
In the WSDL-file, the members...
|
by: yossi.kreinin |
last post by:
Hi!
There is a system where 0x0 is a valid address, but 0xffffffff isn't.
How can null pointers be treated by a compiler (besides the typical
"solution" of still using 0x0 for "null")?
-...
|
by: doncee |
last post by:
I have a list box that is generated on a form by way of a
Parameter Query. Problem is whenever I try to refer to the list
box, i.e., to update the underlying table, I am getting a "null"
value...
|
by: Bob Stearns |
last post by:
I have successfully designed a trigger to make sure a set of fields are
unique if not null. However it fails in one case: when the duplication
occurs among a set of rows being updated...
|
by: mast2as |
last post by:
Hi everyone,
I am trying to implement some specs which specify that an array of
parameter is passed to a function as a pointer to an array terminated
by a NULL chatacter. That seemed fairly easy...
|
by: ADezii |
last post by:
Null as it relates to database development is one of life's little mysteries and a topic of total confusion for novices who venture out into the database world. A Null Value is not zero (0), a zero...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |