Dcount or Count or maybe something else?

[ctrox]

I have the following query run through VB:

SELECT Field1, Field2, Field3, Dcount(Field1,tableName,Field5<7) As Field4
WHERE Field1=a, Field2=b
GROUP BY Field1

The Dcount is working correctly, except that it is ignoring the WHERE and GROUP BY in the SQL statement. It is counting all of the records that are <7 in the entire table and returning the same value in each row.

Am I using Dcount incorrectly or should I be using another method to go about this?

Please help,
ctrox

 

[nickske80]

maybe you can try

COUNT(SELECT field1 FROM table WHERE field5 < 7) AS field4

I think that this could work.

I have never heard of the dcount function, but I am fairly new to SQL.

Hope this helps you,

Nickske.

 

[ctrox]

nickske80,

I tried that, but all it does is return that number of records in the grouping, like it is ignoring the criteria of <7. I hadn't tried your method before, but it gave me the same results that I was getting with Count(Field5<7), that is why I went to Dcount.