not or <> "string" problems

[dunnes]

hello,
im currently using a query which doesnt want to include a certain string ie Lead. the column that i do this criteria search on has many other strings and empty strings, so when i use the criteria not "Lead" or <> "Lead" the filter seems to work but also filters away the blank strings..... is there any other way to filter out the string lead which doesnt also filter out the blank entries????



Cheers
Mike

 

[Andrew Fergus]

Hi Mike
Prefix your criteria with "Is Null Or" (without the quotes)
HTH, Andrew.

 

[dunnes]

genius andrew..... still a bit fluxed with the logic but aslong as it works thats fine with me

Cheers
Mike

 

[Andrew Fergus]

Hi Mike

The logic works such that provided either of the following conditions are met, then display the record :

condition 1 : Is Null (this selects all records where the string is empty)

condition 2 : <>"Lead" (this selects all records where the string does not equal "Lead").

We have to use an Or (instead of an And) because a string can not be null and not equal to something simultaneously - I realise that is 'double speak' because if a field is null then it definitely does not equal something else - but if something is 'null' then there is nothing to compare to the criteria. To summarise : criteria in queries does not work with null records.

Hopefully that isn't adding to the confusion.

Andrew

P.S. I haven't explained that very well but this article might help.