Originally posted by rob7765
I am having trouble with is SQL on a MS Access DB. I have seen other similar post here on this but was not able to solve the problem
sql2="SELECT COUNT(DISTINCT email) AS qtypeople FROM datatbl"
Set recordset2 = Conn.Execute(sql2)
qtypeople = recordset2(0)
Response.Write ("Total Unique Registered People = " & qtypeople)
I get the following error when this runs:
Syntax error (missing operator) in query expression 'COUNT(DISTINCT email)'.
I have also tried:
sql2="SELECT DISTINCT COUNT(email) AS qtypeople FROM datatbl"
Which will return a number but it is the total records rather than the distinct.
As long as you're opening a recordset, why don't you try changing your sql2 string to: "SELECT DISTINCT email FROM datatbl"
Go ahead and open the recordset using your existing syntax and then get the number of distinct records from the recordset's recordcount property: qtypeople = recordset2.recordcount
Note that if you're using DAO a recordset you need to access each of the records before the recordset property will tell you how many records there are. That's because the property tells you how many records have been accessed, not how many total records there are. If you're using ADO, the recordset property telss you the total number of distinct records witout accessing any of the records. If you have an option, I would recommend using ADO for this.