Is it possible to use aggrate function in a SELECT statement?

[Guest]

How should I phrase a Query to run a SELECT statment on a number field of a
table where the criteria is something like the following...
SELECT Table.Field
FROM Table.Field
WHERE Table.Field <>0 AND COUNT ([Table.Field])<>7

The table has numerous NULL fields that I need to ignore and I then need to
find any fields that don't contain a 7 digit number. A simple solution would
be much appricated.
J.
--
Select as appropiate:
You are welcome.
Thank you.
In Anticipation.
Regards.
See Ya!

 

[Douglas J. Steele]

Count tells you how many entries there are, not whether an entry is 7
digits.

If the field is text, try:

SELECT Table.Field
FROM Table.Field
WHERE Table.Field IS NOT NULL AND Len(

.[Field])<>7

If the field is numeric, try:

SELECT Table.Field
FROM Table.Field
WHERE Table.Field IS NOT NULL
AND (

.[Field] < 1000000
OR

.[Field] > 9999999)

 

[Guest]

Try

SELECT Table.Field
FROM Table.Field
WHERE Nz(Table.Field,0) <>0 AND Len([Table.Field])<>7

The Nz function will replace Null with 0, so it wont return Null or Zero's
The Len function will return the number of chr in the field

 

[Guest]

Thanks to all for the very useful relpies.
--
Select as appropiate:
You are welcome.
Thank you.
In Anticipation.
Regards.
See Ya!