Access 2003 qry criteria filters

[ddoblank]

Does anyone have an idea as to why a database would suddenly change how it is
filtering a qry? I haven't made any changes to the qry I have created but now
it is showing data that I don't want. The filter looks something like this:

Order Qty Ship Curr Line Code <------ these are
just the headers

0 <Date()+56 Like "Y31*" or Like "X31*"
0 <Date()+56 Not Like "Isnull"

When I run the qry I am getting line codes that start with Z and F and as
you can see I only want Y and X. This was running fine but something has
changed.

 

[John W. Vinson]

Does anyone have an idea as to why a database would suddenly change how it is
filtering a qry? I haven't made any changes to the qry I have created but now
it is showing data that I don't want. The filter looks something like this:

Order Qty Ship Curr Line Code <------ these are
just the headers

When I run the qry I am getting line codes that start with Z and F and as
you can see I only want Y and X. This was running fine but something has
changed.

Eh?

The text string "Z" is in fact not "like" the text string "Isnull". So it's a
valid hit.

How are you constructing this filter? Did you mean

Not Is Null

as a criterion, or otherwise what is the purpose of this criterion?

 

[John Spencer]

If all you want is records that start with Y31 or X31 then you only need the
first line of criteria. Nulls will automatically be filtered out.

AND with your specific example you could simplify the LIKE to
Like "[xy]31*"


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[ddoblank]

Hi John V

I don't understand how the text string "z" would be a valid hit when I am
only asking to see strings starting with "y" and "x". Is it because I am
using the "Not like IsNull" function, which does seem to work, but maybe not
accurately. Based one what your wrote below, I should be using "Not Is Null".
Would this be more accurate?

 

[ddoblank]

Hi John S,

The reason for the Null criteria, is because the qry was returning null
values. I found this was the only way to get rid of them. Is there something
that is missing in the criteria that may cause the Null values to appear?

I didn't know about the Like "[xy]31*". Thank you for showing that one to
me, I will try it out for sure.

If all you want is records that start with Y31 or X31 then you only need the
first line of criteria. Nulls will automatically be filtered out.

AND with your specific example you could simplify the LIKE to
Like "[xy]31*"


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Eh?

The text string "Z" is in fact not "like" the text string "Isnull". So it's a
valid hit.

How are you constructing this filter? Did you mean

Not Is Null

as a criterion, or otherwise what is the purpose of this criterion?

.

 

[John Spencer]

Not Like "IsNull" means that the value in the field is something other than
the text string "IsNull". So any value in the field at all is valid unless
the entry is "IsNull".

If you are trying to say I don't want NULLs then the criteria in a query is
Is Not Null

IsNull is a VBA function which returns true or false concerning the value of
the argument passed.
IsNull(SomeField)
will return true or false depending on the value of SomeField.

Try my proposal. It should work for you. If it does not, then post back and
tell us what is wrong with the results you receive. Also, please copy and
post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[ddoblank]

If I am understanding things correctly, by me adding the Not Like IsNull, I
have probably confused the program. Removing this and just leaving in my
first statement, the system should work fine and should not return any data
that has no value in this field. It should only return the Like data that I
have asked for.

I did try this and it does work, for now. The only reason why the Not Like
IsNull was entered in the first place was because we were getting some data
that had blank fields.

I don't know, maybe it is just me, but I will keep an eye on it and see what
happens.

thanks for all your help.

 

[John W. Vinson]

If I am understanding things correctly, by me adding the Not Like IsNull, I
have probably confused the program.

By adding a criterion

Not Like IsNull

you simply made a mistake.

This criterion does *NOT* make sense. It's simply wrong!

This criterion means that the text field is not equal to the text string
"IsNull" - a six letter alphabetic text string.

You were apparently assuming that the criterion did something different -
selecting records where the field is not NULL. That goal can be accomplished
by using a criterion of

IS NOT NULL

or

NOT IS NULL

(either one works). That is a different criterion than "Not Like IsNull".