hard coded timeframes

[thatlemhome]

I have an options group that I have hard-coded report timeframes, such as previous month, previous 3 months, etc.

Is there a way to write code that will run the designated timeframe?
In the query I have a month field and year field. Does Access keep the month and year straight if I had simply a month field?

In other words, if I said I wanted to run a report for the previous 6 month timeframe, something like ... (month(date())-7) to (month(date())-1) ...
that crossed the year, so that maybe 2 months in the current year and 4 months in the previous, is there a good way to do this?

Currently the month field runs off code 1-12 and year as a numerical field, but am open to suggestions. However, I am trying to avoid using a "Between" expression which would force staff to enter individual dates instead of a Month for month-end summary data.

Any suggestions would be helpful.

 

[thatlemhome]

I thought I'd post what I'm working on... the first two months work fine, but am concerned that by the time I get to a year's report, this will get out of control trying to define each month as it relates to crossing a year threshold. I'm not concerned with this code in particular, just whether there is a better way to approach hard coding these reports.


stDocName = "rptd_" & Me.ComboReportBox
If Me.Frame57 = 1 Then GoTo CurrentMonth_Click
If Me.Frame57 = 2 Then GoTo LastMonth_Click
If Me.Frame57 = 3 Then GoTo Previous2Months_Click
'If Me.Frame57 = 4 Then GoTo Previous3Months
'If Me.Frame57 = 5 Then GoTo Previous6Months
'If Me.Frame57 = 6 Then GoTo PreviousYear
'If Me.Frame57 = 7 Then GoTo SelfDefined

CurrentMonth_Click:
stDocNameMonth = Month(Date)
stDocNameYear = Year(Date)
GoTo Open_Click

LastMonth_Click:
If Month(Date) = 1 Then stDocNameMonth = 12 Else stDocNameMonth = Month(Date) - 1
If Month(Date) = 1 Then stDocNameYear = Year(Date) - 1 Else stDocNameYear = Year(Date)
GoTo Open_Click

Previous2Months_Click:
If (Month(Date) - 2) < 1 Then stDocNameMonth1 = (Month(Date) - 2) + 12 Else stDocNameMonth1 = Month(Date) - 2
If (Month(Date) - 1) < 1 Then stDocNameMonth2 = (Month(Date) - 1) + 12 Else stDocNameMonth2 = Month(Date) - 1
If (Month(Date) - 2) < 1 Then stDocNameYear1 = Year(Date) - 1 Else stDocNameYear1 = Year(Date)
If (Month(Date) - 1) < 1 Then stDocNameYear2 = Year(Date) - 1 Else stDocNameYear2 = Year(Date)
stDocNameMonth = stDocNameMonth1 & ", " & stDocNameMonth2
stDocNameYear = stDocNameYear1 & ", " & stDocNameYear2
GoTo Open_Click


'Previous3Months_Click:
'Previous6Months_Click:
'PreviousYear_Click:
'SelfDefined_Click:
Open_Click:
'DoCmd.OpenReport stDocName, [acViewReport], , "[Audit Month] ='" & stDocNameMonth & "'" And "[Audit Year] ='" & stDocNameYear & "'"
DoCmd.OpenReport stDocName, [acViewReport], , "[Audit Month] ='" & stDocNameMonth & "'" & " And [Audit Year] ='" & stDocNameYear & "'"
End Sub


Thanks

 

[HiTechCoach]

I would urge youto use a date range and not a separate month and year fields

TIP: I would avoid GoTo's

I would use the DateAdd() to get he desired month/year.

Give me a few minutes a db I will convert your code to use DateAdd() .

 

[HiTechCoach]

I would try something like this:

Dim datReportDate as Date

' calculate the previous period
' must subtract 1 from Me.Frame57 to get how many months to go back

datReportDate  = DateAdd("m", (-1 * ( Me.Frame57 - 1 ) ), Date())

stDocNameMonth = Month(datReportDate )
stDocNameYear = Year(datReportDate )

stDocName = "rptd_" & Me.ComboReportBox

DoCmd.OpenReport stDocName, [acViewReport], , "[Audit Month] ='" & stDocNameMonth & "'" & " And [Audit Year] ='" & stDocNameYear & "'"

End Sub

Hope this helps ...

 

[DCrake]

Here is a pointer to a sample database I have just posted that dynamically does what you want.

Sample Database

David