criteria not working

[JB]

Hello
I am trying to run a query that has 1911 odd rows but only a few rows have
the criteria i'm looking for. there are 5 columns and only 6 or 10 rows have
data in all columns. everything else only has 2 columns with data. In other
words most of the 3 columns are blank.
So i'm entering 'is not null' but when I run, i'm getting hundreds of rows
with empty data in that column. If I do i 'is null' i'm getting 4 rows.
Why on earth is this happening?
Jen

 

[Ken Snell \(MVP\)]

The data in those fields may be an empty string, not NULL.

Use this expression as the criterion for each of those fields:

Len(NameOfField & "") > 0

 

[tina]

sounds like you have zero-length strings stored in those blank-looking
fields. to test that theory, try changing the query criteria to

Is Not Null And <> ""

if that restricts the returned records correctly, then you do have
zero-length strings stored in your table - not a good idea, as you can see.
in that case, suggest you make a copy of your table as backup "just in
case", and then open the table in Design view, go to each field that has a
Data Type of Text, and set the AllowZeroLength property to No. whenever you
create a new table, get in the habit of doing that immediately on each Text
field.

btw, the proper terminology is not rows but records, and not columns but
fields. it's a good idea to use correct terms, especially if you come from
an Excel background; because while rows and columns in Excel intersect to a
specific "address" - a cell - for each stored datum, Access does not work
that way. record order and field order are niceties for viewing, but do
nothing to identify stored data. also, there are specific instances in
working with Access when "row" or "column" IS the proper term, but those are
not referring to data as it's stored in tables. using proper terminology
helps to keep communications clear.

hth

 

[JB]

Thanks for the tips on the null. Was always curious about the zero-length
property.
Also for the other tips re terminology. That's also so useful to know.
Jen

 

[tina]

you're welcome

Thanks for the tips on the null. Was always curious about the zero-length
property.
Also for the other tips re terminology. That's also so useful to know.
Jen