criteria logic mystery

[Tony]

I have a column which either contains "y" or is blank. Yet requesting all
records with "NOT "y"" in that column does not capture all the records with a
blank entry for that column. In other words, the total number of records is
greater than those captured by requesting records with "y" in the column Plus
those which are blank, as captured by "NOT "y"" or "<> "y"".

What is going on here? I've had some other weird programming trouble ever
since I imported the data from an excel spreadsheet. Is this related?

 

[strive4peace]

Hi Tony,

try this for criteria:

field --> fieldname_: nz([fieldname],"")
criteria --> <> "Y"

or, in SQL
WHERE nz([fieldname],"") <> "Y"

NZ converts null values so they can be compared

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[Tony]

It worked!

Do you know where these "null values" come from? Is there another way to
detect and replace them with "real data"? Although it worked, the solution
seems a bit bizzare to a simple user like me.
--
Tony

Hi Tony,

try this for criteria:

field --> fieldname_: nz([fieldname],"")
criteria --> <> "Y"

or, in SQL
WHERE nz([fieldname],"") <> "Y"

NZ converts null values so they can be compared

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

I have a column which either contains "y" or is blank. Yet requesting all
records with "NOT "y"" in that column does not capture all the records with a
blank entry for that column. In other words, the total number of records is
greater than those captured by requesting records with "y" in the column Plus
those which are blank, as captured by "NOT "y"" or "<> "y"".

What is going on here? I've had some other weird programming trouble ever
since I imported the data from an excel spreadsheet. Is this related?

 

[Tony]

Okay. I found another solution which is more understandable to me:

criteria -->IS NULL OR <> "y"

I just wasn't aware of the null case in "blank data entries".
--
Tony

It worked!

Do you know where these "null values" come from? Is there another way to
detect and replace them with "real data"? Although it worked, the solution
seems a bit bizzare to a simple user like me.
--
Tony

Hi Tony,

try this for criteria:

field --> fieldname_: nz([fieldname],"")
criteria --> <> "Y"

or, in SQL
WHERE nz([fieldname],"") <> "Y"

NZ converts null values so they can be compared

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

I have a column which either contains "y" or is blank. Yet requesting all
records with "NOT "y"" in that column does not capture all the records with a
blank entry for that column. In other words, the total number of records is
greater than those captured by requesting records with "y" in the column Plus
those which are blank, as captured by "NOT "y"" or "<> "y"".

What is going on here? I've had some other weird programming trouble ever
since I imported the data from an excel spreadsheet. Is this related?

 

[strive4peace]

Hi Tony,

"Do you know where these "null values" come from? "

An entry with no value Is Null

Is there another way to detect and replace them with "real
data"?

If you have no value for this field, it is better to leave
it blank than to put something where nothing should be.

In Access, Null cannot be compared to a value because there
is nothing to compare ... NZ changes Null from nothing to
something...


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*