Using NOT or <> in a query

[Guest_imported]

I want to write a select statement in my code that will count records that [/b]DO NOT[/B] equal a given value. The report that I'm using does not produce the expected results and I can't figure out why. It appears that the query may be ignoring empty fields. The select statement below contains the problem code in bold print:

"SELECT SUM([Accident Data].[TNOI]) AS Injuries
FROM [Accident Data]
HAVING ((([Accident Data].Date) Between EVAL('[forms]![DiagInfo].BegDate') And EVAL('[forms]![DiagInfo].EndDate')) AND (([Accident Data].[LC Code]) Between 1 AND 3) AND ((([Accident Data].[CD Code]) <> 'PED')) AND (([Accident Data].[StreetNo1])=EVAL('Forms!DiagInfo.[Road1]')) AND (([Accident Data].[StreetNo2])=EVAL('Forms!DiagInfo.Road2')))&quot

 

[tlbroadbent]

Yo need to search for Null columns explicitely.

[Accident Data].[CD Code] Is Null Or [Accident Data].[CD Code] <> 'PED' Terry

People who don't take risks generally make about two big mistakes a year. People who do take risks generally make about two big mistakes a year. -Peter Drucker

 

[MichaelRed]

but i think you want NOT is null ...

since null is 'unknown' it MAY be <> 'PED&quot; and thus should be included innthe total?


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[Larryboybird]

Change your code like this and the value you are evaluating will never be null!

([Accident Data].[CD Code] & &quot;&quot

 

[tlbroadbent]

NULL is neither equal to nor not equal to any specific value because NULL really defines a state rather than a value.

NULLs won't be selected when the only criteria in this example is <> &quot;PED&quot;. However, if you want NULLs included in the result set, the criteria must be IS NULL rather than NOT IS NULL.

Larryboybird makes a good point, also, and provides another method. Terry

------------------------------------
People who don't take risks generally make about two big mistakes a year. People who do take risks generally make about two big mistakes a year. -Peter Drucker

 

[krymat]

How about looking at the help for the Nz()Function, it converts Null or zero length strings to 0