Member Avatar for aquamarine_kath

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 because: n/a
  • 2 Contributors
  • 5 Replies
  • 134 Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by aquamarine_kath
Member Avatar for debasisdas

error on which line ?

Member Avatar for debasisdas

try this

theCount = rs.!MaxID
Edited by debasisdas because: n/a
Member Avatar for aquamarine_kath

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

Member Avatar for aquamarine_kath

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
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.