Using Data Entered as variables for multiple queries

[PatrickJohnson]

Hi! I've written a macro and converted it to VBA which takes an average employee headcount for the 2006 calendar year by taking a sample headcount from the 15th of each month and then averaging the 12 numbers. I have the macro doing this just fine.

-Currently it works something like this (I'll post the exact code below too):
-Macro runs delete query to delete any data in Headcount table
-Macro runs an append query which prompts for a month and year
-Macro sends keystroke of "1" and "{ENTER}" for month, then "2006" and {ENTER} for the year
-Query uses the input data to populate a cdate function which adds "/15/" between the month and year to make a finite date
-Query appends the headcounts for each department to the headcount table for the date specified
-All of this runs again for each month, with only the sendkeys for the month value changed
-A select query averages the numbers for each department in the headcounts table with the avg function in the totals box and displays the average number for each department

This works great... as long as I just want 2006's data. What I would like to do is set this up in VBA so that a user can enter a beginning date and end date and it will pull all the headcounts on the 15th of each month that falls between them, then perform the average query stuff.

This way, if there was a need for an average headcount for a random period of time, the user would just enter a start date and end date into prompts and out comes the result.

Anyone have a good idea as to how to accomplish this?

Function Headcount_Macro()
On Error GoTo Headcount_Macro_Err

DoCmd.SetWarnings False
DoCmd.OpenQuery "OHA Headcount Macro Delete", acViewNormal, acEdit
SendKeys "1{ENTER}", False
SendKeys "2006{ENTER}", False
DoCmd.OpenQuery "OHA Sample Headcount (Specify Month) Append", acViewNormal, acEdit
SendKeys "2{ENTER}", False
SendKeys "2006{ENTER}", False
DoCmd.OpenQuery "OHA Sample Headcount (Specify Month) Append", acViewNormal, acEdit
SendKeys "3{ENTER}", False
SendKeys "2006{ENTER}", False
DoCmd.OpenQuery "OHA Sample Headcount (Specify Month) Append", acViewNormal, acEdit
SendKeys "4{ENTER}", False
SendKeys "2006{ENTER}", False
DoCmd.OpenQuery "OHA Sample Headcount (Specify Month) Append", acViewNormal, acEdit
SendKeys "5{ENTER}", False
SendKeys "2006{ENTER}", False
DoCmd.OpenQuery "OHA Sample Headcount (Specify Month) Append", acViewNormal, acEdit
SendKeys "6{ENTER}", False
SendKeys "2006{ENTER}", False
DoCmd.OpenQuery "OHA Sample Headcount (Specify Month) Append", acViewNormal, acEdit
SendKeys "7{ENTER}", False
SendKeys "2006{ENTER}", False
DoCmd.OpenQuery "OHA Sample Headcount (Specify Month) Append", acViewNormal, acEdit
SendKeys "8{ENTER}", False
SendKeys "2006{ENTER}", False
DoCmd.OpenQuery "OHA Sample Headcount (Specify Month) Append", acViewNormal, acEdit
SendKeys "9{ENTER}", False
SendKeys "2006{ENTER}", False
DoCmd.OpenQuery "OHA Sample Headcount (Specify Month) Append", acViewNormal, acEdit
SendKeys "10{ENTER}", False
SendKeys "2006{ENTER}", False
DoCmd.OpenQuery "OHA Sample Headcount (Specify Month) Append", acViewNormal, acEdit
SendKeys "11{ENTER}", False
SendKeys "2006{ENTER}", False
DoCmd.OpenQuery "OHA Sample Headcount (Specify Month) Append", acViewNormal, acEdit
SendKeys "12{ENTER}", False
SendKeys "2006{ENTER}", False
DoCmd.OpenQuery "OHA Sample Headcount (Specify Month) Append", acViewNormal, acEdit
DoCmd.OpenQuery "OHA Average Headcount from Headcounts Table", acViewNormal, acEdit
DoCmd.SetWarnings True

Headcount_Macro_Exit:
Exit Function

Headcount_Macro_Err:
MsgBox Error$
Resume Headcount_Macro_Exit

End Function

 

[Bat17]

can you post the SQL behind
OHA Sample Headcount (Specify Month) Append",
and I will try to convert it to VBA for you

Peter

 

[PatrickJohnson]

INSERT INTO Headcounts ( [Org Name], [CountOfAssignment Number], [Sample Date] )
SELECT [Assignment History].[Org Name], Count([Assignment History].[Assignment Number]) AS [CountOfAssignment Number], CDate([Enter Sample Month in Number Format] & "/15/" & [Enter Year]) AS [Sample Date]
FROM [Assignment History]
WHERE (((CDate([Enter Sample Month in Number Format] & "/15/" & [Enter Year])) Between [Asg Eff Start Date] And [Asg Eff End Date]))
GROUP BY [Assignment History].[Org Name], CDate([Enter Sample Month in Number Format] & "/15/" & [Enter Year]);

 

[Bat17]

I'll be lucky if this works right first time

Sub UpdateIt(StartDate As Date, EndDate As Date)
Dim intAdjust As Integer
Dim strSql As String
Dim strSqlDate As String
If DateAdd("m", 1, StartDate) > EndDate Then
    MsgBox "Invalid dates", vbCritical, "Input Error"
    Exit Sub
End If
intAdjust = Abs((Day(StartDate) > 15)) ' adjust for dates after 15th of month
StartDate = DateSerial(Year(StartDate), Month(StartDate) + intAdjust, 15)
' clear table
DoCmd.SetWarnings False
CurrentDb.QueryDefs("OHA Headcount Macro Delete").Execute
Do While StartDate <= EndDate
    strSqlDate = Format(StartDate, "MM/DD/YYYY")
    strSql = "INSERT INTO Headcounts ( [Org Name], [CountOfAssignment Number], [Sample Date] ) "
    strSql = strSql & "SELECT [Assignment History].[Org Name], Count([Assignment History].[Assignment Number]) AS [CountOfAssignment Number], #" & strSqlDate & "# AS [Sample Date] "
    strSql = strSql & "FROM [Assignment History] "
    strSql = strSql & "WHERE #" & strSqlDate & "# Between [Asg Eff Start Date] And [Asg Eff End Date] "
    strSql = strSql & "GROUP BY [Assignment History].[Org Name], #" & strSqlDate & "#;"
    Debug.Print strSql
  DoCmd.RunSQL strSql
  StartDate = DateAdd("m", 1, StartDate)
Loop
DoCmd.SetWarnings True
DoCmd.OpenQuery "OHA Average Headcount from Headcounts Table"

Call it passing the date from and date too and hopefully it will do it, but I am better at code than I am SQL and I might not have hacked your SQL right.

Peter

 

[PatrickJohnson]

no luck. i'm thinking there has to be some way to do this with the existing code. maybe declare variables at the beginning for start/end dates like you did, then say if the day of the date is less than 15, change it to 15, if greater than 15, change it to 15 and change the month to month + 1. then adjust the end date the other direction.

then loop with a counter that adds 1 to the month every cycle, and do until counter reaches month of the end date (the one we adjusted earlier).

also, i would probably need to include something each time that says if the month=13 to change it to 1 and add 1 to the year.

it looks like this does most of that but i can't seem to insert it into my macro code.

basically, though it isn't as elegant as true code, i'm okay with leaving the queries as is and applying the variables to the vba code. does that make sense?

 

[Bat17]

ok try it this way

Dim dDate As Date
Function GetDate()
GetDate = dDate
End Function
Sub UpdateIt(StartDate As Date, EndDate As Date)
Dim intAdjust As Integer
Dim strSql As String
Dim strSqlDate As String
If DateAdd("m", 1, StartDate) > EndDate Then
    MsgBox "Invalid dates", vbCritical, "Input Error"
    Exit Sub
End If
intAdjust = Abs((Day(StartDate) > 15)) ' adjust for dates after 15th of month
StartDate = DateSerial(Year(StartDate), Month(StartDate) + intAdjust, 15)
' clear table
DoCmd.SetWarnings False
CurrentDb.QueryDefs("OHA Headcount Macro Delete").Execute
Do While StartDate <= EndDate
    dDate = Format(StartDate, "MM/DD/YYYY")
CurrentDb.QueryDefs("OOHA Sample Headcount (Specify Month) Append").Execute
  StartDate = DateAdd("m", 1, StartDate)
Loop
DoCmd.SetWarnings True
DoCmd.OpenQuery "OHA Average Headcount from Headcounts Table"
End Sub

The Dim dDate As Date line needs to be up ate the top of the mode after the options.
Change the CDate bits in your query for GetDate()

Test on a copy!

peter

 

[PatrickJohnson]

I'm so sorry for my novice skill level, but I am totally confused. Can one of you point out which line of code is the prompt for the Start Date and End Date? Also, it looks like in the second code batch that it just adds 1 to the month every loop. If it does this with no parameters and it has to cross a calendar year, wouldn't that throw it off when it hits 13?

 

[Bat17]

Assuming you are using a form with two fields formatted as date, one to start and one to end then on a button you woud have something like

call UpdateIt(Me.StartDateField, Me.EndDateField )

The year will roll over OK

Peter