Request Option Group Code Fix & Addition

  • Thread starter Thread starter Bayou BoB
  • Start date Start date
B

Bayou BoB

Hello!

I'm using the following option group code to filter reports by
age......month, quarter, and year. I am looking to do two things here.
First I'm looking for a fix for the "quarterly" code...It is not
working properly and needs to be adjusted.

Secondly I am looking to add options here...primarily the following,

"Last 14 Days"
"Last 30 Days"
"Last 90 Days"
"Last 180 Days"
"Last 365 Days"

What would the appropriate Code be for this? Here is the case code I'm
presently using and want to add options to...


Private Sub Command11_Click()
On Error GoTo Err_Command11_Click

Dim stDocName As String
Dim stWhere As String

stDocName = "CLI_DetailedAttendance_Rep"
Select Case [Frame0]

Case 1 'Current month
stWhere = "Month([Date]) = " & Month(Date) & "And Year([Date])
= " & Year(Date)

Case 2 'Current quarter
stWhere = "DatePart("q", [Date]) = " & DatePart("q", Date)& "
And Year([Date]) = "& Year(Date)

Case 3 'Current year
stWhere = "Year([Date]) = " & Year(Date)

End Select
DoCmd.OpenReport stDocName, acPreview, , stWhere



Many Thanks!

Kevin
 
Hello!

I'm using the following option group code to filter reports by
age......month, quarter, and year. I am looking to do two things here.
First I'm looking for a fix for the "quarterly" code...It is not
working properly and needs to be adjusted.

Secondly I am looking to add options here...primarily the following,

"Last 14 Days"
"Last 30 Days"
"Last 90 Days"
"Last 180 Days"
"Last 365 Days"
Click to expand...

I take it that "last 30 days" does not actually mean "last 30 days",
but "the current month"? and that if you ran it on the 2nd you'ld just
want two days of data?
What would the appropriate Code be for this? Here is the case code I'm
presently using and want to add options to...


Private Sub Command11_Click()
On Error GoTo Err_Command11_Click

Dim stDocName As String
Dim stWhere As String

stDocName = "CLI_DetailedAttendance_Rep"
Select Case [Frame0]

Case 1 'Current month
stWhere = "Month([Date]) = " & Month(Date) & "And Year([Date])
= " & Year(Date)

Case 2 'Current quarter
stWhere = "DatePart("q", [Date]) = " & DatePart("q", Date)& "
And Year([Date]) = "& Year(Date)
Click to expand...

Using Date as a fieldname may be part of the problem! An alternative
date range would be

strWhere = "[Date] >= #" & DateSerial(Year(Date()), (Month(Date()) -
1) \ 3) * 3 + 1, 1) & "# AND [Date] < #" & DateSerial(Year(Date()),
Month(Date()) - 1) \ 3) * 3 + 4, 1)
 
Back
Top