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

J

JohnM

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!
 
D

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)
 
O

Ofer Cohen

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
 
J

JohnM

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