SELECT COUNT(Distinct surname)

[laura]

I am trying to count how many unique instances of a surname there are in my
database and obviously the result will be a number. I want to build an SQL
statement in VB but cannot seem to achieve it. I am not sure what to do. I
read that

"SELECT COUNT (DISTINCT surname) FROM tblClients" might do the trick, but I
keep getting a syntax error message saying that there is a missing operator.
I cannot work it out. Nor can I work out how to put the result into a field
on a Report - i.e., it should look something like this, but it doesn't
work:-

Dim db As Database
Dim rs As Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT COUNT (DISTINCT surname) FROM tblClients")

.....

but I want the result to show in a field on my report.. how?

me.text10.recordsource = .... ?

Thanks
Laura TD

 

[Tom Ellison]

Dear Laura:

I misunderstood. John got it right!

Tom Ellison

 

[laura]

Thank you John and also Tom for suggestions and help.

It looks like a nested query, John, and thank you - it works fine, just what
I need...

Thanks
Laura

 

[laura]

Thanks Tom, see my reply to him.

Laura

Dear Laura:

I misunderstood. John got it right!

Tom Ellison