I have fields UserID, Status and etc. The status of the user will be either active or inactive. I want to count how many UserID of Active users only.
I have a code here but it's not working..It says "Invalid or Unqualified reference".
Can you please help me check this out? Thanks. :)
(The field UserID is my primary key and it is varchar)

Dim theCount As String
dim rs as adodb.recordset
set rs = new adodb.recordset

rs.Open "select count(UserID) as MaxID from user_tab where Status = '" & "Active" & "'", conn, adOpenStatic, adLockOptimistic

If rs.EOF = False Then
theCount = !MaxID
End If

MsgBox "" & theCount

Edited 5 Years Ago by aquamarine_kath: n/a

When I changed it to theCount = rs.!MaxID, the line gets red..
I tried rs(!MaxID).. but the error is still the same..

I already solved it.
This code works now...

strSql = "select count(UserID) as MaxID from user_tab where Status = '" & "Active" & "'"
With rs
.Open strSql, conn, adOpenStatic, adLockOptimistic

If .EOF = False Then
theCount = !MaxID
End If

MsgBox "" & theCount
End With
This question has already been answered. Start a new discussion instead.