where clause returns incorrect results

[Guest]

I'm new to Access having only used dBase for my queries sometime ago.
I have written the following query how ever it returns no records at all. If
I change the [where to = "Y" I get all the Y records. I have tried <> "Y" but
this gives the same result.

SELECT MASTER01.*, MASTER02.*
FROM MASTER02 RIGHT JOIN MASTER01 ON (MASTER02.POSTCODE02 =
MASTER01.POSTCODE01) AND (MASTER02.TOWN02 = MEMF01.TOWN01)
WHERE (((MASTER01.F01FLAG) Not Like "Y"))
ORDER BY MASTER01.POSTCODE01, MASTER01.LNAME01;
Can anyone see my error, I believe this was a problem in Access some time
ago but has now been fixed so I assume it's my error.
I'm using Access 2003 with all the lastest patches and updates.

 

[Allen Browne]

John, is there any chance that one of these fields contains a trailing space
or other strange character?

The Like operator behaves differently than the equality operators in this
case. Details in:
Trailing spaces give inconsistent query results
at:
http://allenbrowne.com/bug-15.html

 

[Guest]

Thanks for the reply Alan. The field F01FLAG is a single character field it
does not always contain a character it is mostly either Y or blank but may
also contain other characters.
Regards
--
John Melbourne

John, is there any chance that one of these fields contains a trailing space
or other strange character?

The Like operator behaves differently than the equality operators in this
case. Details in:
Trailing spaces give inconsistent query results
at:
http://allenbrowne.com/bug-15.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I'm new to Access having only used dBase for my queries sometime ago.
I have written the following query how ever it returns no records at all.
If
I change the [where to = "Y" I get all the Y records. I have tried <> "Y"
but
this gives the same result.

SELECT MASTER01.*, MASTER02.*
FROM MASTER02 RIGHT JOIN MASTER01 ON (MASTER02.POSTCODE02 =
MASTER01.POSTCODE01) AND (MASTER02.TOWN02 = MEMF01.TOWN01)
WHERE (((MASTER01.F01FLAG) Not Like "Y"))
ORDER BY MASTER01.POSTCODE01, MASTER01.LNAME01;
Can anyone see my error, I believe this was a problem in Access some time
ago but has now been fixed so I assume it's my error.
I'm using Access 2003 with all the lastest patches and updates.

 

[Allen Browne]

Okay, it might be worth experimenting with asking Access what's going on.

Create a query without critiera. Then type in some calculate fields into the
Field row, such as these:
FieldLength: Len([F01FLAG])
IsY: ([F01FLAG] = 'Y')
LikeY: ([F01FLAG] <> 'Y')
and so on. See if you can identify what's going on for these records that
are not returned correctly.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the reply Alan. The field F01FLAG is a single character field
it
does not always contain a character it is mostly either Y or blank but may
also contain other characters.
Regards
--
John Melbourne

John, is there any chance that one of these fields contains a trailing
space
or other strange character?

The Like operator behaves differently than the equality operators in this
case. Details in:
Trailing spaces give inconsistent query results
at:
http://allenbrowne.com/bug-15.html

message

I'm new to Access having only used dBase for my queries sometime ago.
I have written the following query how ever it returns no records at
all.
If
I change the [where to = "Y" I get all the Y records. I have tried <>
"Y"
but
this gives the same result.

SELECT MASTER01.*, MASTER02.*
FROM MASTER02 RIGHT JOIN MASTER01 ON (MASTER02.POSTCODE02 =
MASTER01.POSTCODE01) AND (MASTER02.TOWN02 = MEMF01.TOWN01)
WHERE (((MASTER01.F01FLAG) Not Like "Y"))
ORDER BY MASTER01.POSTCODE01, MASTER01.LNAME01;
Can anyone see my error, I believe this was a problem in Access some
time
ago but has now been fixed so I assume it's my error.
I'm using Access 2003 with all the lastest patches and updates.

 

[Albert D. Kallal]

Perhaps you might want to expand here:

if you use:

WHERE F01FLAG) = "Y"

You get all of the "Y" guys.

However, when you go:

WHERE F01FLAG) <> "Y"

do you get some records...or none records? Remember, your field could have:

" " - a blank
"" - a zero length string
null - a null value

Now, this is means that all null fields will NOT work when you go:

WHERE F01FLAG) <> "Y"

The above will work for " ", or "N", or any other characters. However, that
list WILL NOT include null in the list. What this means is that you need to
sit down, and MAKE A DESIGN decision as to if you going to allow nulls in
your database or not. A real nice friendly mess is to allow both "" (zero
length strings) and nulls in a field. The reason for this is then to find
all blank fields, you have to go:

WHERE (F01FLAG is null) or (F01FLAG) = "")

And, if you imported the data from another table, then you might even have a
space. Thus, you will need:

WHERE (F01FLAG is null) or (F01FLAG) = "") or (F01FLAG) = " ")

That is HUGE amount of work just to find the blank fields....

Further, is this some data that you imported, linked to another table, or
was this data entered via a form in ms-access? (this is critical answer..as
it will explain MUCH as to what kind of data you have in that field).

 

[Guest]

John,

Is there a typo in your query?

You show: "Master02.Town02 = MEMF01.Town01"

but I don't see anything table in your FROM clause that is named or aliased
as MEMF01.

 

[Guest]

Albert,

Ref your comment:

And, if you imported the data from another table, then you might even have a
space. Thus, you will need:

Wny not just use:

WHERE LEN(TRIM(NZ(F01FLAG),"")) = 0

to test for null, empty string, or any string with nothing but blanks.

Dale

 

[Guest]

Thank you this has explained my mistake, in believing that a null and a space
would be treated the same.
The problem is as you have pointed out I have no control over the data as it
is provided to me from another company and I have no control over their data.
So I have to write from the point of what they request. i.e. in this case I
must accept all records that are not "Y", so if I understand correctly ‘ I
can except all Is null or <>â€Y†and that will give me “â€, “ “, “Nâ€, “$†and
any other charter. I will take the file and input some test data to test this.

 

[Guest]

Dale,
Thanks for the replies. The typo occurred when I changed the query
statements to make them generic. I have answered Alberts posting which
appears at this stage to answer my question. I had run this as a filter and
it worked with out a problem until I wanted to include it in my SQL. The
records all contain at this point in time “Y†or null but I can not rely on
this in the future as I have no control over what is very bad data.
--
John Melbourne

John,

Is there a typo in your query?

You show: "Master02.Town02 = MEMF01.Town01"

but I don't see anything table in your FROM clause that is named or aliased
as MEMF01.

--
Email address is not valid.
Please reply to newsgroup only.

I'm new to Access having only used dBase for my queries sometime ago.
I have written the following query how ever it returns no records at all. If
I change the [where to = "Y" I get all the Y records. I have tried <> "Y" but
this gives the same result.

SELECT MASTER01.*, MASTER02.*
FROM MASTER02 RIGHT JOIN MASTER01 ON (MASTER02.POSTCODE02 =
MASTER01.POSTCODE01) AND (MASTER02.TOWN02 = MEMF01.TOWN01)
WHERE (((MASTER01.F01FLAG) Not Like "Y"))
ORDER BY MASTER01.POSTCODE01, MASTER01.LNAME01;
Can anyone see my error, I believe this was a problem in Access some time
ago but has now been fixed so I assume it's my error.
I'm using Access 2003 with all the lastest patches and updates.

 

[Albert D. Kallal]

Thank you this has explained my mistake, in believing that a null and a
space
would be treated the same.

The problem is as you have pointed out I have no control over the data as
it
is provided to me from another company and I have no control over their
data.

So, that is not really a problem. I you are importing the data into an
existing table, then
you can define that table to not allow the zero length strings. Further,
after you import,
you could also execute a trim on the data field to remove any extra blank
spaces.

Doing these two steps would fix much of your problems. And, if you import
on a frequent bases, then you likely should code a button to do the import
(it would be only one click for the user..and the data could be
imported...trimmed
and you be done).

So I have to write from the point of what they request. i.e. in this case
I
must accept all records that are not "Y", so if I understand correctly ' I
can except all Is null or <>"Y" and that will give me "", " ", "N", "$"
and
any other charter. I will take the file and input some test data to test
this.

Yes..that sounds about right. As for running a "process" that fixes the data
that you import, it really depends on if you plan to keep the data for a
long
time, or if these are one time reports you throw away. For ease of use, and
future considering, you might want to run some process that cleans up
the data after importing....