Why i am getting nulls as result in query?

[jaryszek]

Hi,

i would like to check corresponding fields from 2 seperated tables:

tblVolumeNames
VolumeNameID VolumeName
1 Vol1
2 Vol2
3 Null (blank)
4 Vol3


Temp_VolumeChanges
ID VolumeName Name
1 Vol1 Paul
2 Null (blank) Jacek
3 Null (blank) John

I want to check which volumeName is present in Temp_VolumeChanges and is NOT present in tblVolumeNames table.

So i created sql like here:

SELECT t2.VolumeName
FROM Temp_VolumeChanges AS t2 LEFT JOIN tblVolumeNames AS t1 ON Nz(t2.VolumeName,"Null") = Nz(t1.VolumeName,"Null")
WHERE t1.VolumeName) Is Null;

This result should show nothing (because null exists also in tblVolumeNames) but i am getting:

Why is that?
What can i do to avoid this?

Please help,
Jacek

 

[Gasman]

Because yoiu are setting Null to "Null"?

 

[jaryszek]

Yes, because in other case Gasman if you will not use Nz function Access will not see joins with nulls. You can not join empty fields in Access.

Please take a look into:
https://www.access-programmers.co.uk/forums/showthread.php?p=1605987#post1605987

Best,
Jacek

 

[isladogs]

Null is never equal to anything not even another null.
You cannot assign a string value "Null" in your Nz function.
That isn't a null string.

Try changing you query to design view. Its not allowed.

 

[jaryszek]

Yes this is true.

But Nz Function is returning any string if Variant field is null:

You can use the Nz function to return zero, a zero-length string (" "), or another specified value when a Variant is Null. For example, you can use this function to convert a Null value to another value and prevent it from propagating through an expression

In this case it means when i will create select query and use Nz function i will get Null there as string (as Nz result)
And this string i want to join.

Best,
Jacek

 

[jaryszek]

BTW.

I am using this sql also:

SELECT DISTINCTROW t2.* 
FROM   tblvolumes AS t2 
       LEFT JOIN qryim_sourcevolumes AS t1 
              ON ( Nz(t2.linuxmountpassno, "null") = 
                   Nz(t1.linuxmountpassno, "null") ) 
                 AND ( Nz(t2.linuxmountfreq, "null") = 
                       Nz(t1.linuxmountfreq, "null") ) 
                 AND 
       ( Nz(t2.mountoptions, "null") = Nz(t1.mountoptions, "null") ) 
                 AND ( Nz(t2.lvmstripesize, "null") = 
                       Nz(t1.lvmstripesize, "null") ) 
                 AND ( Nz(t2.volumegroup, "null") = Nz(t1.volumegroup, "null") ) 
                 AND ( Nz(t2.providerid, "null") = Nz(t1.providerid, "null") ) 
                 AND ( Nz(t2.volumeid, "null") = Nz(t1.volumeid, "null") ) 
                 AND ( Nz(t2.earlymount, "null") = Nz(t1.earlymount, "null") ) 
                 AND 
       ( Nz(t2.servicelevel, "null") = Nz(t1.servicelevel, "null") ) 
                 AND ( Nz(t2.providerfulfillment, "null") = 
                       Nz(t1.providerfulfillment, "null") ) 
                 AND ( Nz(t2.consumerfulfillment, "null") = 
                       Nz(t1.consumerfulfillment, "null") ) 
                 AND ( Nz(t2.writeacceleratorenabled, "null") = 
                       Nz(t1.writeacceleratorenabled, "null") ) 
                 AND ( Nz(t2.caching, "null") = Nz(t1.caching, "null") ) 
                 AND ( Nz(t2.function, "null") = Nz(t1.function, "null") ) 
                 AND ( Nz(t2.description, "null") = Nz(t1.description, "null") ) 
                 AND ( Nz(t2.formatcommand, "null") = 
                       Nz(t1.formatcommand, "null") ) 
                 AND ( Nz(t2.sharemode, "null") = Nz(t1.sharemode, "null") ) 
                 AND 
                 ( Nz(t2.percentsnapshotspace, "null") = 
                   Nz(t1.percentsnapshotspace, "null") ) 
                 AND ( Nz(t2.filesystemtype, "null") = 
                       Nz(t1.filesystemtype, "null") ) 
                 AND ( Nz(t2.disknumber, "null") = Nz(t1.disknumber, "null") ) 
                 AND ( Nz(t2.physicaldiskmodelidfk, "null") = 
                       Nz(t1.physicaldiskmodelidfk, "null") ) 
                 AND ( Nz(t2.tierid, "null") = Nz(t1.tierid, "null") ) 
                 AND ( Nz(t2.volumenameidfk, "null") = 
                       Nz(t1.volumenameidfk, "null") ) 
                 AND ( Nz(t2.environmentid, "null") = 
                       Nz(t1.environmentid, "null") ) 
                 AND ( Nz(t2.ordinal, "null") = Nz(t1.ordinal, "null") ) 
WHERE  t1.ordinal IS NULL 
       AND t1.environmentid IS NULL 
       AND t1.volumenameidfk IS NULL 
       AND t1.tierid IS NULL 
       AND t1.physicaldiskmodelidfk IS NULL 
       AND t1.disknumber IS NULL 
       AND t1.filesystemtype IS NULL 
       AND t1.percentsnapshotspace IS NULL 
       AND t1.sharemode IS NULL 
       AND t1.subjectcomponent IS NULL 
       AND t1.formatcommand IS NULL 
       AND t1.description IS NULL 
       AND t1.function IS NULL 
       AND t1.caching IS NULL 
       AND t1.writeacceleratorenabled IS NULL 
       AND t1.consumerfulfillment IS NULL 
       AND t1.providerfulfillment IS NULL 
       AND t1.servicelevel IS NULL 
       AND t1.earlymount IS NULL 
       AND t1.volumeid IS NULL 
       AND t1.providerid IS NULL 
       AND t1.volumegroup IS NULL 
       AND t1.lvmstripesize IS NULL 
       AND t1.mountoptions IS NULL 
       AND t1.linuxmountfreq IS NULL 
       AND t1.linuxmountpassno IS NULL;

and have fields with nulls and this is working like a charm and the strange thing is that in the first case this is not working...

Jacek

 

[jaryszek]

I have just replaced statement like this:

( t2.percentsnapshotspace = t1.percentsnapshotspace 
                    OR ( t2.percentsnapshotspace IS NULL 
                         AND t1.percentsnapshotspace IS NULL ) )

with statement like this:

 Nz(t2.percentsnapshotspace,"NULL") = Nz(t1.percentsnapshotspace,"NULL")

 

[isladogs]

In your query SQL you are specifying a Null value for one field (in RED) and trying to join that same field to another field (in BLUE). Therefore you are trying to check a situation where null=null ...which is meaningless/impossible

SELECT t2.VolumeName
FROM Temp_VolumeChanges AS t2 LEFT JOIN tblVolumeNames AS t1 ON [B][COLOR="Blue"]Nz(t2.VolumeName,"Null") = Nz(t1.VolumeName,"Null"[/COLOR])[/B]
WHERE [B][COLOR="DarkRed"]t1.VolumeName) Is Null[/COLOR][/B];

Its a totally meaningless query

 

[jaryszek]

Hmm thank you, i do not udenrstand it.

This is a left join. If in one table i have "Null" and in second table i have "Null" it should check that in first table there is "Null" in second is also "Null" so we do not matching fields. So table1 has the same values as in table2...So if there is no match i should get true null as result and this is why i used whre clause...

So how can i write this query?

 

[jaryszek]

and Colin so why my big sql (the same mechanism) is working?
I checked twice. If i am deleting something from Temp table and want to see all fields which should be deleted from database table - i have empty field now- i see query result with 1 row. So this is working.
Why?

Jacek

 

[isladogs]

I'm not totally clear why result you are expecting
This will give ID=3 as the Volume Name is null in both tables

SELECT Temp_VolumeChanges.ID, Temp_VolumeChanges.VolumeName, tblVolumeNames.VolumeName
FROM Temp_VolumeChanges INNER JOIN tblVolumeNames ON Temp_VolumeChanges.ID = tblVolumeNames.VolumeNameID
WHERE (((Temp_VolumeChanges.VolumeName) Is Null) AND ((tblVolumeNames.VolumeName) Is Null));

If that's not the required result then you will need to specify what output you would expect. Alternatively go back to your lengthy 17 join thread and study that again including the approach suggested by MajP

You big sql is far too long to go through. Perhaps it gives a result but only using part of the criteria specified?

 

[jaryszek]

Hi,

thank you very much !

I investigated further and my SQL do not have sense indeed.

But issue occurs only in this specific situation.
I just retrive ID field (not all table) and it is working.

If i will take my long SQL it will work like a charm - to reproduce situation to get nulls in big sql all fields after WHERE clause should be nulls and have corresponding nulls in 2 tables. Only then i would get not expected results. In my case this is safe.

For others you can use MajP method with VBA if you can have situation : comparing whole null record to another null record (i think it is very rare).
But statement with Nz is perfectly safe:

Nz(t2.percentsnapshotspace,"NULL") = Nz(t1.percentsnapshotspace,"NULL")

If you can have nulls like i have in first post here - you can retrive another field (like ID in my case) or try another method.

Best wishes,
Jacek