Solved Update Query changing nulls to string (1 Viewer)

jaryszek

Registered User.
Local time
Today, 15:17
Joined
Aug 25, 2016
Messages
756
Hi Guys,

i am using query to update nulls to "Null" string on temp table:

Code:
UPDATE tblVolumeOSMountOptionsTemp
SET OSType = Nz(OSType,"Null"),
OSDistro = Nz(OSDistro,"Null"),
OSVersion = Nz(OSVersion,"Null"),
StorageProtocol = Nz(StorageProtocol,"Null"),
ProtocolVersion = Nz(ProtocolVersion,"Null"),
ProviderFileSystemType = Nz(ProviderFileSystemType,"Null"),
MountPath = Nz(MountPath,"Null"),
OSVolumeName = Nz(OSVolumeName,"Null"),
ConsumerFileSystemType = Nz(ConsumerFileSystemType,"Null"),
ConsumerFulfillment = Nz(ConsumerFulfillment,"Null"),
ProviderFulfillment = Nz(ProviderFulfillment,"Null"),
ProviderMountOptions = Nz(ProviderMountOptions,"Null"),
ConsumerMountOptions = Nz(ConsumerMountOptions,"Null"),
ProviderLinuxMountFreq = Nz(ProviderLinuxMountFreq,"Null"),
ConsumerLinuxMountFreq = Nz(ConsumerLinuxMountFreq,"Null"),
ProviderLinuxMountPassno = Nz(ProviderLinuxMountPassno,"Null"),
ConsumerLinuxMountPassno = Nz(ConsumerLinuxMountPassno,"Null")

IT is not working because last 4 columns are numbers.
1. It is possible to specify what kind if field we have(like number) to change Null to ...?

2. ...To what? How you are handling updating nulls in number fields columns?

I have 0 also there so i can not use 0 i think and afraid it will not work with joins...

Please help,
Jacek
 

Ranman256

Well-known member
Local time
Today, 18:17
Joined
Apr 9, 2015
Messages
4,339
You are changing null to a string called "null"?
Why would you want numbers as null?
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 09:17
Joined
Jan 20, 2009
Messages
12,849
A number field can't store words.
What is wrong with an actual Null?

If you want to see something else in reports you can format the output then.
One simple way to do this for number columns is by putting the following in the Format property of the control
Code:
;;;"Null"
 

jaryszek

Registered User.
Local time
Today, 15:17
Joined
Aug 25, 2016
Messages
756
Hi Guys,

i am changing nulls because they are not joined properly where i am doing left joins.

Jacek
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 09:17
Joined
Jan 20, 2009
Messages
12,849
I don't really understand the join problem but can you substitute with an out of scope value such as a negative number?

Alternatively you might be able to put an expression in the join but you would have to do it in SQL view.
However I'm not sure what Access supports any more as I mostly work in SQL Server.
 

jaryszek

Registered User.
Local time
Today, 15:17
Joined
Aug 25, 2016
Messages
756
Thank you

Code:
However, it may be safest to manage NULL values by first setting them to valid but unused values in each field of the import & destination fields

For example, update null dates to 01/01/9999 and number fields to an unlikely value such as -1000000
You will need to do this for each field separately in the two tables. For example:

UPDATE tblData SET tblData.StartDate = #1/1/9999# WHERE (((tblData.StartDate) Is Null));
After completion run a further update query to revert the modified values in the destination table back to null.

Once again do this for each field separately. For example:

UPDATE tblData SET tblData.StartDate = Null WHERE (((tblData.StartDate)=#1/1/9999#));

I found isladgos solution for this!
Closing topic
Jacek
 

Isaac

Lifelong Learner
Local time
Today, 15:17
Joined
Mar 14, 2017
Messages
8,738
I think you already know this probably, but I'll throw it out there anyway. You have a big data modeling problem if you are worried about joining on null fields. In the databases I've had that have been well designed (that hasn't always been the case, I'll admit!)--I only find myself joining on key fields which are almost always autonumber and are never null. I wonder if you could take the time you are spending on this 'converting things to the word Null" project, which I've seen posts about before, and instead spend it getting data into the proper parent-child tables with key values. It would pay off nicely.
 

jaryszek

Registered User.
Local time
Today, 15:17
Joined
Aug 25, 2016
Messages
756
thank you my friend. Sometimes like in real life you do not have choice. I am getting just very ugly raw data in csvs without primary keys and can not change model...
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:17
Joined
Feb 28, 2001
Messages
27,001
You have belabored this issue in another post and I still have to bring up this fact: Saying that two records are alike because they both have nulls in the same position is a massive logical error. When you get bad data, sometimes the solution is to pre-convert the data before trying to use it. I.e. if you don't like the problem, change it. If you can't change it BEFORE it comes in, change it AFTER it comes in by using an intermediate table where you have the luxury of changing that table's structure - including imposition of keys on the imported version of that dataset.

I understand that when you get someone else's data you often have a steaming pile of crap to shovel. I am with you on the idea of trying to find common elements. But that search for somehow matching one null to another is not theoretically sound.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:17
Joined
Feb 28, 2001
Messages
27,001
@jaryszek - PLEASE understand I am not slamming you. I admit I might be slamming the problem, but that pile fell into your lap and you are trying to make some sense of it. I deeply appreciate that struggle. I've had cases where I had to analyze various network logs and they had variable syntax based on things that DID or DIDN'T happen. So I appreciate dealing with imprecise data. I wish you good luck in your search for meaning.

My warning is merely that it might not be a good idea to draw inferences of similarity based on nulls, meaning "absence of data."
 

jaryszek

Registered User.
Local time
Today, 15:17
Joined
Aug 25, 2016
Messages
756
thank you once again The_Doc_Man, no i have very nice feeling because of youe help! I am realy appreciate your patient explanations !
Really helps!
 

Users who are viewing this thread

Top Bottom