View SQL Count Records

Hello,

How can I view the count from the code below?

??  msgbox "My Count is " & mycount

Dim mycount As String
mycount = "SELECT Count(*) AS MyCount " & vbCrLf & _
"FROM BackgroundCheck " & vbCrLf & _
"WHERE (((BackgroundCheck.Background) Not Like ""Access Denied"" And (BackgroundCheck.Background)=""Waiver Denied"" And (BackgroundCheck.Background)=""Waiver Granted"") AND ((BackgroundCheck.DateNeededCheck)<DateAdd(""yyyy"",-1,Date()-1)));"

Open in new window

Ernest GroggAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
try this
sub MyCount()
dim xCount as integer
xCount=Dcount("*","BackgroundCheck","[Background] In('Waiver Denied','Waiver Granted') And [DateNeededCheck] <DateAdd('yyyy',-1,Date()-1)")

msgbox "My count is " & xCount

end sub
0
 
ste5anConnect With a Mentor Senior DeveloperCommented:
E.g.

Dim Sql As String  
Dim rs As DAO.Recordset
Dim myCount As Long
Dim Condition As String

Sql = _
  "SELECT Count(*) " & _
  "FROM BackgroundCheck " & _
  "WHERE Background IN ( 'Waiver Denied', 'Waiver Granted' ) " & _
  "AND DateNeededCheck <DateAdd('yyyy',-1,Date()-1);" 

Set rs = CurrentDb.OpenRecordset(Sql)
myCount = rs.Fields(1).Value
 
Condition = _
  "Background IN ( 'Waiver Denied', 'Waiver Granted' ) " & _
  "AND DateNeededCheck <DateAdd('yyyy',-1,Date()-1);" 
  
myCount = DCount("*", "BackgroundCheck", Condition)

Open in new window

0
 
Ernest GroggAuthor Commented:
Great Solutions!

Thanks,

I had to change ste5an's just a bit to get it to work:  (the condition had a syntax error, so I removed and changed the fields to 0)

I also had the string slightly wrong, so this below I had to have it looking just for the 1 .  

AND

Rey Obrero (Capricorn1) was good to go also...granting both as great solutions.

Dim Sql As String
Dim rs As DAO.Recordset
Dim mycount As Long

Sql = _
  "SELECT Count(*) " & _
  "FROM BackgroundCheck " & _
  "WHERE Background IN ( 'Access Granted' ) " & _
  "AND DateNeededCheck <DateAdd('yyyy',-1,Date()-1);"

Set rs = CurrentDb.OpenRecordset(Sql)
mycount = rs.Fields(0)
 

MsgBox "Total Record needing moved: " & mycount

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.