character length

[Ben]

Hi 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
--

 

[Dale Fye]

Ben,

If the field is NULL, then neither of your criteria will return that row.

Try somthing like this:

SELECT NZ([Flag], "NULL") as FlagValue, Count(*) as Freq
FROM myTable
GROUP BY NZ([Flag], "NULL")

This should give you a list of the "values" in the [Flag] field, and the
number of times they occur in your table.

If you want all the records where [FLAG] <> "myflag" then try

SELECT *
FROM myTable
WHERE [Flag] IS NULL OR [Flag] <> "myflag"

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[Ben]

Dale, John -

Thank you both. It worked.
The null was the culprit.

Ben


--

Ben,

If the field is NULL, then neither of your criteria will return that row.

Try somthing like this:

SELECT NZ([Flag], "NULL") as FlagValue, Count(*) as Freq
FROM myTable
GROUP BY NZ([Flag], "NULL")

This should give you a list of the "values" in the [Flag] field, and the
number of times they occur in your table.

If you want all the records where [FLAG] <> "myflag" then try

SELECT *
FROM myTable
WHERE [Flag] IS NULL OR [Flag] <> "myflag"

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

Hi 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