null value causing query funnies...

[Brad Pears]

here's a strange one in Access 2000...

I have a table that contains a "country" field to identify a record related
to a specific country - if need be... This field is NOT required. It would
only have a country code in there (i.e. USA, CDA) if the row pertains to
that country only...

So, I did a query where I only want rows that are not = "USA"... (I wanted
all rows having country code = "CDA" and any that country code that is blank
or null)

The criteria I entered for country was <> "USA".

When I ran it, the query did not return any rows that had a null value for
the country - as I thought it would. I had to include "is null or <> USA" in
order to get all rows also having null values...

Should I have expected this?? I did not - and now am wondering how many
other queries I have that possibly may not be returning the correct number
of rows...

Is this by design or is there something amiss??

Thanks,

Brad

 

[Guest]

can you post your sql so we can look at it.

 

[Graham R Seach]

Brad,

This is expected behaviour. Database technology hasn't yet reached the DWIM
(do what I mean) level, and in my view, nor should it. If you want the query
to return null values in addition to other values, you have to specify null
values in your criteria.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

 

[Guest]

One other note....when dealing with text in criteria in queries, you are
better off using Not Like "USA" rather than <> "USA". I thought they were
the same thing, but some interesting things have happened to me in not
getting data using the latter form. I think I saw somewhere in these boards
that you should use Not Like for Text format and <> for Number format.

 

[Graham R Seach]

Not Like "USA" is not the same as Not Like "*USA*". I think you may be
referring to instances when a user enters a space at either end of the
string, in which case, Trim(somefield) M< "USA" will do.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

 

[Graham R Seach]

Oops - very cold morning + fat fingers! That last line should read:
string, in which case, Trim(somefield) <> "USA" will do.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html