0

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 by aquamarine_kath: n/a

2
Contributors
5
Replies
6
Views
5 Years
Discussion Span
Last Post by aquamarine_kath
0

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

0

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.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.