CODEWITHSUNDEEP
sqlvbams-accessrecordset
sambob_628
sambob_628

Reputation: 133

Access - recordset search to return a fieldcount with 2 different criteria

I’m looking to display some key data on the home page.. things like, open non conformances for 1 day, 7 days more than 7 days… or count of jobs that should have been complete yesterday that are still open Etc. Ect

With little success, I’m trying to get a recordset search to return a fieldcount based on 2 different criteria.. and then I was going to merge the two criteria… but I cant get it working

The first query is trying to count the records between a date range between now and -7 days

vSQL = "SELECT count(*) AS FieldCount FROM tblNonConformance WHERE [Date] =" & "[Date] Between #" & Format(Now(), "mm\/dd\/yyyy hh\:nn\:ss") & "# And #" & Format(Now() - 7, "mm\/dd\/yyyy hh\:nn\:ss") & "#"

The second query is counting the number of records that are still open (from a yes/no field in the db)

vSQL = "SELECT count(*) AS FieldCount FROM tblNonConformance WHERE [NCR Clsd?]=" & False

The fieldcount in both cases is coming back as empty (the logic is if I can’t get them working individually I have no hope of nesting them)

Public Sub Tester124()
Dim vSQL As String
Dim rs As Recordset

'Creating a count SQL statement for the Date Range
   vSQL = "SELECT count(*) AS FieldCount FROM tblNonConformance WHERE [Date] =" & "[Date] Between #" & Format(Now(), "mm\/dd\/yyyy hh\:nn\:ss") & "# And #" & Format(Now() - 7, "mm\/dd\/yyyy hh\:nn\:ss") & "#"
'Create a count SQL statement for the closed status
   'vSQL = "SELECT count(*) AS FieldCount FROM tblNonConformance WHERE [NCR Clsd?]=" & False
   
'Set the recordset to variable rs and have it return the query
    Set rs = CurrentDb.OpenRecordset(vSQL)

'Displaying the results.. i will do more with this when i have a usable result
    With rs
      MsgBox (FieldCount)
      End With
End Sub

Any suggestions while the fieldcount comes back as empty?

Upvotes: 0

Views: 148

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270763

Don't convert the date to a date constant. I'm not sure why the second wouldn't work, but you can combine the two as:

SELECT SUM(IIF([Date] < NOW() AND [Date] >= DATEADD("d", -7, NOW()), 1, 0)) as cnt1,
       SUM(IIF([NCR Clsd?], 0, 1)) as cnt2
FROM tblNonConformance;

Upvotes: 1

Related Questions