Why does not equal to exclude null values?

[MattioMatt]

I have a query that pulls a list of vulnerabilities.

In that there is a column called "TicketState". The value is either, Open, Resolved or it's empty.

I've got a where clause in the query to not show anything that is resolved as the list on the form is for open tickets. However I've noticed the query is excluding any records that have a null value when I have this where clause on.

How can I fix this?

SELECT tblVuln.VulnID, tblAssets.AssetID, tblAssets.IP, tblAssets.ServerName, tblVuln.DNS, tblVuln.QID, tblVuln.Title, tblVuln.Severity, tblVuln.PCIVuln, tblVuln.VulnStatus, tblVuln.TicketState, tblVuln.FirstDetected, tblVuln.LastDetected, tblVuln.[Times Detected]
FROM tblAssets INNER JOIN tblVuln ON tblAssets.IP = tblVuln.IP
WHERE (((tblVuln.TicketState)<>"Resolved"))
ORDER BY tblAssets.ServerName;

 

[isladogs]

Try this:

SELECT tblVuln.VulnID, tblAssets.AssetID, tblAssets.IP, tblAssets.ServerName, tblVuln.DNS, tblVuln.QID, tblVuln.Title, tblVuln.Severity, tblVuln.PCIVuln, tblVuln.VulnStatus, tblVuln.TicketState, tblVuln.FirstDetected, tblVuln.LastDetected, tblVuln.[Times Detected]
FROM tblAssets INNER JOIN tblVuln ON tblAssets.IP = tblVuln.IP
WHERE ((Nz(tblVuln.TicketState,'')<>"Resolved"))
ORDER BY tblAssets.ServerName;

 

[MattioMatt]

This is perfect thank you! It works!

 

[The_Doc_Man]

The technical reason for the failure was that the math routines inside Access are simple-minded. They don't make assumptions.

If you attempt, for example, a simple comparison using = or <>, that comparison operator is actually implemented as a dyadic function, meaning it takes two inputs and produces one output. This is true because the code in VBA or SQL is not being compiled; it is being interpreted by some hidden code processor. When you provide NULL inputs to one side of the dyad, it blows up in your face because NULL is "ain't nothin' there!"

Even if VBA code or SQL clauses WERE fully compiled, that NULL would be presented to the math processor as something that isn't a number. (In fact, it isn't ANYTHING.) Hardware won't go for that any better than the interpretive function.

 

[isladogs]

a dyadic function

Well I'd never heard of that ... learnt something new there Doc ....

And just to add to Doc's comments, you can't compare null values as no two nulls can be equal to each other.

In other words, there

ain't nothin' there

but its a different kind of nothin' each time

 

[The_Doc_Man]

Just like there are different flavors of infinity, there are different flavors of NULL and NEITHER one should actually be compared.

As to the terms, they are DYADIC (2 inputs) and MONADIC (1 input). There are higher level version of these "IC"s but nobody used them much. That is because the pioneers of math always tried to break things down to their simplest parts. Finite Automata theory proved that all other functions could be broken down to MONADIC or DYADIC operations. So for example, SIN(x) and -x are MONADIC. X^2 is DYADIC (with a constant of 2 as one operand). X*Y is DYADIC. X*Y*Z is TWO DYADICS in a row, and place the parentheses where you want for (X*Y)*Z or X*(Y*Z). Compilers do this behind the scenes in order to analyze your expressions in VBA.

I called these functions because they are functions in VBA. Even -X is a function because VBA isn't a compiler. It is a semi-compiler that generates pseudo-code. I don't know EXACTLY what it generates at the bit level, but I have seen a dozen other similar systems. They all generate a call to a routine followed by a list of addresses for the inputs. The interpreter therefore just keeps track of where you are in the code sequence and calls the individual operation (internal) routine. That routine then grabs the addresses to find the required values. Sounds clunky, but for machines where you don't know ahead of time what instruction set you are using, it is incredibly important.

And you have to remember that when Access version 2 came out some 20+ years ago, the common PCs available still included chipsets based on the Intel 8088, 8086, the '186, '286, and '386 chips. I don't recall whether the '486 chip was out at that time but I know the Pentium ('586) was later. (I used Ac v2 on a 386 myself.) And let's not even THINK about the I7 chipsets.

All of those processors had different instruction sets - overlapping because they were based on the Intel 8088, but with more instructions available as the model numbers went up. That is the reason for using the pseudo-compiler. You just load the pseudo-code execution routines appropriate for YOUR chipset, as determined at run time. Of course, once you have the pseudo-code method set up, it is HELL to switch over to a true compiler.

If you understand Windows architecture, you will realize that Windows O/S does the same thing. They call it the "Hardware Abstraction Layer" (or HAL), and there are actually quite a few of them available in a library which, on Win7, is in the path

C:\Windows\System32\hal.dll

The system links itself with whichever HAL it needs from that library. Then when it needs to execute an instruction that might not be in the "common" set, they call a routine from HAL and if the instruction is present, it just executes - but if it is NOT present, that function is emulated by more complex subroutine.

But 'nuff for the history lesson.

 

[isladogs]

Thanks Doc though please don't test me on my understanding of all of that

 

[The_Doc_Man]

Wouldn't think of it. Since I'm retired, I don't do tests that often any more. Just test myself now and then.