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

 

[jrbarnett]

Hi,

DCount is an external function call, so in order to get that working you have to add your additional criteria to the third parameter. Thus:

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

You don't need to DCount field1 as it only returns a count of rows - and there is code to speed up access on counting * rather than a specific field.

John

 

[ctrox]

That still doesn't give me what I need. It is filtering with the additional parameters within the Dcount function, but gives me the same number through all the rows. I need this for example (Field5 being the Dcount result, and parameters of Field1=a and Field2=b):

Field1 Field2 Field3 Field5
a b d 3
a b e 7
a b f 4

Right now the query would return for this example:

Field1 Field2 Field3 Field5
a b d 14
a b e 14
a b f 14


Any ideas? I'm stuck.