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.
3  1521   JustJim 407
Recognized Expert Contributor
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
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 407
Recognized Expert Contributor
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
to be optional, it's very definition is it's a necessary link to the
parent table and part of the definition. If it's optional it shouldn't
be part of the definition of a table and should be in a linking table
instead.
Comments?
|
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 string or NULL.
Becaus i have a if statement:
if ....
cursor.execute(".................insert NULL ..............")
if ....
|
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 specified", such as
having a float member called Value, and a bool member called ValueSpecified.
This instructs the XML Serializer to omit that attribute altogether if it
wasn't "Specified". But how can I tell it to omit the XML element
altogether?
|
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 of the object are marked as nilable.
I generated the client classes using VS.NET 2003. After the creation, I got
the class-definition of the objects returned by the WebService too. BUT,
only the System.String members where marked to be nullable,...
|
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")?
- AFAIK C allows "null pointers" to be represented differently then
"all bits 0". Is this correct?
- AFAIK I can't `#define NULL 0x10000' since `void* p=0;' should work
just like `void* p=NULL'. Is this correct?
| | |
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 from any row or field I refer to. Have tried using "after
update" events as well as a button click event to no avail. The
debug window always shows "null" values when I bring it up while
trying to examine my code\events. Is there a way to get back to...
|
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 simultaneously. The trigger is
below. Can you suggest a way to improve it to eliminate the failure? I
don't know enough about triggers to know if changing BEFORE UPDATE to
AFTER UPDATE, for instance, might do what I want. This is only my 5th
trigger.
BTW:...
|
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 to implement. I had a
special Malloc function that would allocated the number of needed
bytes for the objects i needed to store + 1 additional byte to save
the NULL character
/*!
|
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 (0) length string, an empty Field, or no value at all - so exactly what is Null? The purpose of this Topic is hopefully to explain what a Null Value is, discuss some peculiarities about Nulls, show how we can detect them, and finally, how to convert...
|
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 effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
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 tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
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, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
