Function To Create List Of Month Year Between Date Range

[kfschaefer]

I am looking for code to create an array or function that will create a list of dates, including future dates based on a date range (Between Date() and DateAdd("M", 60, Date())

I am creating a crosstab query when I need to display future months as column headings for the next 5 years and I do not want to create a table with over 120 fields to handle this. I am looking for a way to do this on the fly. Note the list of months need to adjust every month - this is for Forecasting so the list of months must float accordingly. I have tried various options within the crosstab but it will not display future months only those months that contain date.

Any assistance is greatly appreciated.

 

[vbaInet]

List of dates being days?

Loop through the date starting from the initial date, adding 1 to it and saving into the array or appending it to the SQL of the crosstab query.

 

[kfschaefer]

No it will be months and Years, hence the 60 month calculation

Between Date() and DateAdd("M", 60, Date())

could you please help me to get started what would the code look like if I was to create an array and use it as the column headings.

K

 

[vbaInet]

You can't use an array as Column Headings for a crosstab.

Static Column Headings of a Crosstab query is defined as follows:

PIVOT Format([DateField], "mmm yyyy") In ('Jan 2010', 'Feb 2010', ... etc)

You might want to try it out to understand what's going on.

To get the months and years:

dim i as integer, strPivot as string

for i = 1 to 59
    strpivot = strPivot & "'"dateadd("m", i, Date) & "', "
next

Trim of the trailing single quote and comma at the end, affix it to the PIVOT line to build your sql.

All aircode of course

 

[kfschaefer]

Thanks for the suggestions, however, using the hardcoded dates in the pivot in statement would require changing the query each time the report ran- so that is only show the next 60 months from the current month & Year. Also your sugestion would require hard coding 60 months worth of dates - isn't there a limit to an IN statement?

Can your suggestion for the code be called in the Pivot part of the crosstab.

Pivot funtionaName(date)

and would this return the future months?

K

 

[vbaInet]

Thanks for the suggestions, however, using the hardcoded dates in the pivot in statement would require changing the query each time the report ran- so that is only show the next 60 months from the current month & Year.

Correct! There's no other way.

Also your sugestion would require hard coding 60 months worth of dates

Not correct! The months are built up in code so that's not hard coding.

... - isn't there a limit to an IN statement?

I think there is a limit but I don't remember off the top of my head. But 60 is not at all near it.

Can your suggestion for the code be called in the Pivot part of the crosstab.

Pivot funtionaName(date)

Did you try it? It won't work.

If there was another way I would have suggested it.

 

[boblarson]

Correct! There's no other way.

I am proud to say - YES THERE IS another way and it can be dynamic!!!! WOO HOO!

Use this function to modify your crosstab:

Function BuildCrossTab(strCrosstabName, intNumMonthsToUse As Integer, intStartMonth As Integer, intStartYear As Integer)
    Dim strSQL As String
    Dim strForMonths As String
    Dim i As Integer
    Dim qdf As DAO.QueryDef
    Dim dte As Date
    ' sets the querydef to the existing crosstab query
    Set qdf = CurrentDb.QueryDefs(strCrosstabName)
    ' makes sure i is at 0
    i = 0
    ' sets the intial date
    dte = DateSerial(intStartYear, intStartMonth, 1)
    
    ' iterates through the number of months you have passed to the function
    Do Until i = intNumMonthsToUse
        strForMonths = strForMonths & Chr(39) & Format(dte, "mmm yyyy") & Chr(39) & ","
        i = i + 1
        ' changes the date to the next month
        dte = DateAdd("m", 1, dte)
    Loop
   ' removes the last comma
    strForMonths = Left(strForMonths, Len(strForMonths) - 1)
    ' adds the necessary surrounding bits
    strForMonths = "In (" & strForMonths & ")"
    
   ' You put your query here like this and modify it as shown  to include the month string
    strSQL = "TRANSFORM Count(Orders.OrderID) AS CountOfOrderID " & _
             "SELECT Orders.ShipName, Count(Orders.OrderID) AS [Total Of OrderID] " & _
             "FROM Orders " & _
             "GROUP BY Orders.ShipName " & _
             "PIVOT Format([OrderDate],'mmm yyyy') [B][COLOR=red]" & strForMonths & [/COLOR][/B]";"
             
    ' changes the existing query's underlying SQL to the new one with the new months
    qdf.SQL = strSQL
    
    qdf.Close
End Function

and I didn't make this totally reusable but you could if you passed the SQL string as a parameter as well.

 

[vbaInet]

WOO HOO!

Haha!!

By static I mean the Column Headings. You're a building a dynamic range of column headings but you're still using Static Column Headings in the Crosstab if that makes sense.

This was what I was getting across to the OP.

 

[boblarson]

Oh, vbaInet actually had the same thing in shorthand up there:

dim i as integer, strPivot as string
for i = 1 to 59
    strpivot = strPivot & "'"dateadd("m", i, Date) & "', "
next

 

[boblarson]

but you're still using Static Column Headings in the Crosstab if that makes sense.

So static in that they are explicitly defined (regardless of how that definition gets to the query)?

 

[vbaInet]

That's right Bob.

 

[boblarson]

Well, so I think perhaps there has been too much emphasis put on the word "static" without explaining that in Access a crosstab is not like Excel where you can choose various ways of displaying it. In Access there is really one way and it is not dynamic for any of it, really without some query definition manipulation.

I kept hearing that you have to manually type your column headings into the query and that there was no way to dynamically change them. But it is another bit of a miscommunication when it is possible to "dynamically" change them. And I say "dynamically" in the sense that once your query is saved, you can have Access change the column headings but with code and however you choose to implement the choosing of those columns.

 

[vbaInet]

I kept hearing that you have to manually type your column headings into the query and that there was no way to dynamically change them. But it is another bit of a miscommunication when it is possible to "dynamically" change them. And I say "dynamically" in the sense that once your query is saved, you can have Access change the column headings but with code and however you choose to implement the choosing of those columns.

Yup. Just what I meant. I suppose it's good to lay emphasis on the words "static" and "dynamic" when speaking of Crosstab Column Headings.