Showing totals from previous months, What am I missing?

Question

when I run this code it isn't showing the right records I need to see. I'm missing something somewhere but I'm not seeing it. The idea is for this to pull a count of all completed, in progress and Not started from the previous month and give me the totals.

Sub Update()
StatusCount "Completed"
StatusCount "In Progress"
StatusCount "Not Started"
'StatusCount "Moved to Cleanup"
'StatusCount "N/A"
'StatusCount "This is a new category" ', Now - 2, Now + 3

End Sub

Sub StatusCount(ByVal status As String, Optional start_date As Date, Optional end_date As Date)
Dim i As Variant
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qd As DAO.QueryDef

Set db = CurrentDb

Dim SQL As String
If start_date = 0 Or end_date = 0 Then
    SQL = "insert into statussummary (Count,mmyy,status) Select count(*), [created], [research status] " & _
           "from [gwc master list]" & _
           "where [research status] = '" & status & "'" & _
           "group by [research status], [created]"
Else
    SQL = "insert into statussummary (Count,mmyy,status) Select count(*), [created],[research status] " & _
           "from [gwc master list]" & _
           "where [research status] = '" & status & "'" & _
           " and [created] > #" & start_date & "# and created < #" & end_date & "#" & _
           "group by [research status], [created]"
End If

db.Execute (SQL)
rc = db.RecordsAffected
If rc = 0 Then
    Debug.Print status & ": " & rc
    SQL = "insert into statussummary (Count,status) values (" & rc & ", '" &   status & "')"
    db.Execute (SQL)
End If
End Sub

Any help is appreciated -D

Count and Status are both reserved words and may well be tripping you up. I would change them to something else or at a bare minimum put square brackets around them. — Minty, Apr 13 '17 at 16:18
So I've been messing with my count statements and I think I'm on to something but I'm missing something here since it's only showing the first record and not everything from the previous month to the current month. here is what I have so far, (this is part of the first count statement in the code above) `StatusCount "Completed", DateSerial(Year(Date), Month(Date) - 1, 1), DateSerial(Year(Date), Month(Date) + 1, 1)` — Deke, Apr 13 '17 at 16:58
[How to debug dynamic SQL in VBA](http://stackoverflow.com/a/1099570/3820271) — Andre, Apr 13 '17 at 17:33
The variable names "start_date" and "end_date" imply inclusive values. Have you considered comparing the dates with `<=` and `>=` instead of `<` and `>`? — C Perkins, Apr 15 '17 at 01:11
Yeah but I get a compile error when attempting to use `StatusCount "Completed", <=DateSerial(Year(Date), Month(Date) - 1, 1), >=DateSerial(Year(Date), Month(Date) + 1, 1)` — Deke, Apr 17 '17 at 18:50

score 0 · Accepted Answer · answered Apr 13 '17 at 17:24

0

First, you need to change your database table field names. As mentioned, "status" is reserved, as is "count". Therefore, your three fields should be something like [R_COUNT], [CREATED], [R_STATUS]. Then, you restructure your VBA code like so:

Sub StatusCount(ByVal status_var As String, Optional start_date As Date, Optional end_date As Date)

This should solve the first conflict with "status". Then, you modify your first SQL statement to specify the correct fields you are inserting into statussummary (as I changed the names to above). Don't forget that you missed a space or two in the code before the continuation marks.

If start_date = 0 Or end_date = 0 Then
    SQL = "insert into statussummary ([R_COUNT], [CREATED], [R_STATUS]) Select COUNT(*), [created], [research status] " & _
           "from [gwc master list] " & _
           "where [research status] = '" & status_var & "'" & _
           " group by [research status], [created];"
Else
    SQL = "insert into statussummary ([R_COUNT], [CREATED], [R_STATUS]) Select COUNT(*), [created], [research status] " & _
           "from [gwc master list] " & _
           "where [research status] = '" & status_var & "'" & _
           " and [created] > #" & start_date & "# and created < #" & end_date & "#" & _
           " group by [research status], [created];"
End If

Finally, fix your last statement:

rc = db.RecordsAffected
If rc = 0 Then
    Debug.Print status & ": " & rc
    SQL = "insert into statussummary ([R_COUNT], [R_STATUS]) values (" & rc & ", '" & status_var & "');"
    db.Execute (SQL)
End If
End Sub

answered Apr 13 '17 at 17:24

SandPiper

2,816
5
30
52

This is almost perfect but it still isn't giving me the range of records I'm trying to query up. I need it to show me all the "completed", "In progress" and "Not Started" from the previous month. These are being counted from the [Research Status] Field. I also eventually need these to sort by month but that's something for another time. Any suggestions? – Deke Apr 13 '17 at 17:55
You would probably need something like `Select COUNT(*), [created], [research status] from [gwc master list] where [research status] in (""Completed"", ""In progress"", ""Not Started"") and [created] > #" & start_date & "# and created < #" & end_date & "#" & group by [research status], [created];"` – SandPiper Apr 13 '17 at 21:08
If you want more than just the specified status from your input box, you would need to make your query look something more like that ^ – SandPiper Apr 13 '17 at 21:09
@Deke What do you mean by "it still isn't giving me the range"? What range is it giving you? Your question has the term "previous month", but then the sample code passes absolutely no date into the StatusCount function. The only code that calculates any sort of "previous month" is in your comments, but that was just "messing". I suggest editing the question to be more clear. – C Perkins Apr 15 '17 at 01:08
by "range" what I'm looking for is a total number of records from the beginning of the previous month through the current day. I then need to show the totals for each month individually. For example if there are 20 records for the month of March and 10 for April so far I need to be able to show those metrics. Hope that clarifies what I'm trying to do. – Deke Apr 18 '17 at 16:34

Showing totals from previous months, What am I missing?

1 Answers1