character length

[Ben]

All,

I am encountering a perculiar problem with Access.

I have field called flag and it has three different kinds of value. The
field is a text type field. Each I did a select statement to find seach for
records of a particular flag it returns the right number of records. But
when I do another select to view all records that are not of this type, the
select statement returned only half of all the records. The table has about
34K records and there are only 28 records with this flag.

I tried using 1) SELECT * FROM mytable WHERE flag <> "myflag" 2)SELECT *
FROM mytable WHERE flag NOT LIKE "*myflag*"
when I tried each of the above, I only got half of the records back, but I
was expecting (34K-28) records. I even did a Len() function to ascertain it
was 5 characters in length. Can you share some thoughts?

Thanks,

Ben

--

 

[Jeff Boyce]

Ben

I'm not entirely clear from your description ...

It sounds like you are saying that your 'flag' field could contain numbers,
pictures or text ("three different kinds of values"). Do you mean there are
only three values that might show up in that field (e.g., "Red", "Green",
"Blue")?

One way to confirm that the field holds what you expect is to use a query.
Create a new query in design view, click the Totals button (sigma, looks
like a sideways "M"), and select the [flag] field (whatever you named it).

Use the GroupBy aggregation and run the query. You should see one row per
'value' in that field. Do you have only three?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Ben]

Jeff,
Yes, that is what I was trying to say.

--

Ben

I'm not entirely clear from your description ...

It sounds like you are saying that your 'flag' field could contain numbers,
pictures or text ("three different kinds of values"). Do you mean there are
only three values that might show up in that field (e.g., "Red", "Green",
"Blue")?

One way to confirm that the field holds what you expect is to use a query.
Create a new query in design view, click the Totals button (sigma, looks
like a sideways "M"), and select the [flag] field (whatever you named it).

Use the GroupBy aggregation and run the query. You should see one row per
'value' in that field. Do you have only three?

Regards

Jeff Boyce
Microsoft Office/Access MVP

All,

I am encountering a perculiar problem with Access.

I have field called flag and it has three different kinds of value. The
field is a text type field. Each I did a select statement to find seach
for
records of a particular flag it returns the right number of records. But
when I do another select to view all records that are not of this type,
the
select statement returned only half of all the records. The table has
about
34K records and there are only 28 records with this flag.

I tried using 1) SELECT * FROM mytable WHERE flag <> "myflag" 2)SELECT *
FROM mytable WHERE flag NOT LIKE "*myflag*"
when I tried each of the above, I only got half of the records back, but I
was expecting (34K-28) records. I even did a Len() function to ascertain
it
was 5 characters in length. Can you share some thoughts?

Thanks,

Ben

 

[Ben]

Jeff,

you are correct, the flag field is of text type and can have 3 different
values.
I tried using the group by and it does show three values. But still when I
use SELECT * FROM mytable WHERE flag<>"myflag"
It still shows only half of the records.

Ben


--

Ben

I'm not entirely clear from your description ...

It sounds like you are saying that your 'flag' field could contain numbers,
pictures or text ("three different kinds of values"). Do you mean there are
only three values that might show up in that field (e.g., "Red", "Green",
"Blue")?

One way to confirm that the field holds what you expect is to use a query.
Create a new query in design view, click the Totals button (sigma, looks
like a sideways "M"), and select the [flag] field (whatever you named it).

Use the GroupBy aggregation and run the query. You should see one row per
'value' in that field. Do you have only three?

Regards

Jeff Boyce
Microsoft Office/Access MVP

All,

I am encountering a perculiar problem with Access.

I have field called flag and it has three different kinds of value. The
field is a text type field. Each I did a select statement to find seach
for
records of a particular flag it returns the right number of records. But
when I do another select to view all records that are not of this type,
the
select statement returned only half of all the records. The table has
about
34K records and there are only 28 records with this flag.

I tried using 1) SELECT * FROM mytable WHERE flag <> "myflag" 2)SELECT *
FROM mytable WHERE flag NOT LIKE "*myflag*"
when I tried each of the above, I only got half of the records back, but I
was expecting (34K-28) records. I even did a Len() function to ascertain
it
was 5 characters in length. Can you share some thoughts?

Thanks,

Ben

 

[John W. Vinson]

I tried using 1) SELECT * FROM mytable WHERE flag <> "myflag" 2)SELECT *
FROM mytable WHERE flag NOT LIKE "*myflag*"
when I tried each of the above, I only got half of the records back, but I
was expecting (34K-28) records. I even did a Len() function to ascertain it
was 5 characters in length. Can you share some thoughts?

It's not altogether clear what's going on but... be aware that NULL fields
will not be returned by this criterion. NULL is not equal to "myflag", but
it's not *unequal* to it either! Any comparison criterion with NULL will
return NULL, and the record won't be returned.

Try

SELECT * FROM mytable WHERE flag <> "myflag" OR flag IS NULL