How can I find the count of fields in vba?

[hanski]

Hi.

I have this:

Dim conn As New ADODB.Connection
Dim str1 As New ADODB.Recordset

Set conn = CurrentProject.Connection

str1.Open "select count(Field1) as Allfields from dbase group by
Field1", conn, adOpenDynamic, adLockOptimistic


I have in my dbase 450 data in Field1. 200 of them are different kind
of, so I thougt that my Allfields result should be 200, but it is
allways 1 !

Why does it find only 1? If I take the "group by field1" away, then
Allfields is 450!!

What is wrong?

hannu

 

[strive4peace]

Hi Hannu,

SELECT Count(Field1) AS NumUniqueValues
FROM tablename
WHERE (Field1 In (select distinct [Field1] from tablename));


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*