Filter Report using Option Group

  • Thread starter Thread starter Chuck
  • Start date Start date
C

Chuck

I have a form based on qrySalesReport. The form has an option group
[ReportType] and an unbound combo box, the combo box indicates a year (ie.
2007) and i would like all records from the query to be within 2007
[IssueDate] and when option group 2 is selected, i would like to filter on
field [Product] to only include records where the product name begins with
"INS *" and is wild carded after that. here is the start of my code. thanks
in advance



Private Sub Preview_Report_Click()
On Error GoTo Err_Preview_Report_Click

Dim stDocName As String
Dim strWhere As String

Select Case Me.[ReportType].Value
Case 1 ' Show all Records
'DoCmd.ShowAllRecords
stDocName = "rptSalesInsurance"
DoCmd.OpenReport stDocName, acPreview
Case 2 ' Insurance Report
strWhere = tblInsurance.Product Like "INS %"
stDocName = "rptSalesInsurance"
DoCmd.OpenReport stDocName, acPreview, , strWhere

End Select


Exit_Preview_Report_Click:
Exit Sub

Err_Preview_Report_Click:
MsgBox Err.Description
Resume Exit_Preview_Report_Click

End Sub
 
Hi Chuck,

if [IssueDate] contains just the year (I don't think so) do in this way.
if [IssueDate] contains a date in the strwhere strings instead of
tblInsurance.issuedate wirte year(tblInsurance.issuedate)

Private Sub Preview_Report_Click()
On Error GoTo Err_Preview_Report_Click

Dim stDocName As String
Dim strWhere As String

Select Case Me.[ReportType].Value
Case 1 ' Show all Records
'DoCmd.ShowAllRecords
strWhere = "tblInsurance.issuedate=" & "'" & nameoftheyearcombo & "'"
stDocName = "rptSalesInsurance"
DoCmd.OpenReport stDocName, acPreview
Case 2 ' Insurance Report
strWhere = "tblInsurance.Product Like INS* and
tblInsurance.issuedate=" & "'" & nameoftheyearcombo & "'"
stDocName = "rptSalesInsurance"
DoCmd.OpenReport stDocName, acPreview, , strWhere

End Select


Exit_Preview_Report_Click:
Exit Sub

Err_Preview_Report_Click:
MsgBox Err.Description
Resume Exit_Preview_Report_Click

End Sub

HTH Paolo
 
Hi Paolo, [IssueDate] is a date field. The combo box as 4 entries;
FYTD which should equal between Oct 21, 2007 and Oct 20, 2008
2007 which should equal between Jan 1, 2007 and Dec 31, 2007
2008 and so on....
2009 etc

Dont i have to translate the combobox value to an expression and apply to
[IssueDate] so records can be filtered.

Paolo said:
Hi Chuck,

if [IssueDate] contains just the year (I don't think so) do in this way.
if [IssueDate] contains a date in the strwhere strings instead of
tblInsurance.issuedate wirte year(tblInsurance.issuedate)

Private Sub Preview_Report_Click()
On Error GoTo Err_Preview_Report_Click

Dim stDocName As String
Dim strWhere As String

Select Case Me.[ReportType].Value
Case 1 ' Show all Records
'DoCmd.ShowAllRecords
strWhere = "tblInsurance.issuedate=" & "'" & nameoftheyearcombo & "'"
stDocName = "rptSalesInsurance"
DoCmd.OpenReport stDocName, acPreview
Case 2 ' Insurance Report
strWhere = "tblInsurance.Product Like INS* and
tblInsurance.issuedate=" & "'" & nameoftheyearcombo & "'"
stDocName = "rptSalesInsurance"
DoCmd.OpenReport stDocName, acPreview, , strWhere

End Select


Exit_Preview_Report_Click:
Exit Sub

Err_Preview_Report_Click:
MsgBox Err.Description
Resume Exit_Preview_Report_Click

End Sub

HTH Paolo



Chuck said:
I have a form based on qrySalesReport. The form has an option group
[ReportType] and an unbound combo box, the combo box indicates a year (ie.
2007) and i would like all records from the query to be within 2007
[IssueDate] and when option group 2 is selected, i would like to filter on
field [Product] to only include records where the product name begins with
"INS *" and is wild carded after that. here is the start of my code. thanks
in advance



Private Sub Preview_Report_Click()
On Error GoTo Err_Preview_Report_Click

Dim stDocName As String
Dim strWhere As String

Select Case Me.[ReportType].Value
Case 1 ' Show all Records
'DoCmd.ShowAllRecords
stDocName = "rptSalesInsurance"
DoCmd.OpenReport stDocName, acPreview
Case 2 ' Insurance Report
strWhere = tblInsurance.Product Like "INS %"
stDocName = "rptSalesInsurance"
DoCmd.OpenReport stDocName, acPreview, , strWhere

End Select


Exit_Preview_Report_Click:
Exit Sub

Err_Preview_Report_Click:
MsgBox Err.Description
Resume Exit_Preview_Report_Click

End Sub
Click to expand...
Click to expand...
 
I made a couple of changes on the form; i have 2 new text boxes StartDate and
EndDate. I use a Short Date input mask. I have a record with IssueDate
14-sep-2007
If i put in date range 28/9/2007 to 30/10/2007 i dont see the Sep-14 record.
If i put in date range 01/10/2007 to 30/10/2007 i DO see the Sep-14 record.
Why is that. Here is my Case 2 code:

Case 2 ' INS - Insurance Report
strWhere = "[Product] Like 'INS *' AND [IssueDate] BETWEEN #" & _
Me.StartDate & "# AND #" & Me.EndDate & "#"
stDocName = "rptSalesInsurance"
Debug.Print strWhere
DoCmd.OpenReport stDocName, acPreview, , strWhere




Chuck said:
Hi Paolo, [IssueDate] is a date field. The combo box as 4 entries;
FYTD which should equal between Oct 21, 2007 and Oct 20, 2008
2007 which should equal between Jan 1, 2007 and Dec 31, 2007
2008 and so on....
2009 etc

Dont i have to translate the combobox value to an expression and apply to
[IssueDate] so records can be filtered.

Paolo said:
Hi Chuck,

if [IssueDate] contains just the year (I don't think so) do in this way.
if [IssueDate] contains a date in the strwhere strings instead of
tblInsurance.issuedate wirte year(tblInsurance.issuedate)

Private Sub Preview_Report_Click()
On Error GoTo Err_Preview_Report_Click

Dim stDocName As String
Dim strWhere As String

Select Case Me.[ReportType].Value
Case 1 ' Show all Records
'DoCmd.ShowAllRecords
strWhere = "tblInsurance.issuedate=" & "'" & nameoftheyearcombo & "'"
stDocName = "rptSalesInsurance"
DoCmd.OpenReport stDocName, acPreview
Case 2 ' Insurance Report
strWhere = "tblInsurance.Product Like INS* and
tblInsurance.issuedate=" & "'" & nameoftheyearcombo & "'"
stDocName = "rptSalesInsurance"
DoCmd.OpenReport stDocName, acPreview, , strWhere

End Select


Exit_Preview_Report_Click:
Exit Sub

Err_Preview_Report_Click:
MsgBox Err.Description
Resume Exit_Preview_Report_Click

End Sub

HTH Paolo



Chuck said:
I have a form based on qrySalesReport. The form has an option group
[ReportType] and an unbound combo box, the combo box indicates a year (ie.
2007) and i would like all records from the query to be within 2007
[IssueDate] and when option group 2 is selected, i would like to filter on
field [Product] to only include records where the product name begins with
"INS *" and is wild carded after that. here is the start of my code. thanks
in advance



Private Sub Preview_Report_Click()
On Error GoTo Err_Preview_Report_Click

Dim stDocName As String
Dim strWhere As String

Select Case Me.[ReportType].Value
Case 1 ' Show all Records
'DoCmd.ShowAllRecords
stDocName = "rptSalesInsurance"
DoCmd.OpenReport stDocName, acPreview
Case 2 ' Insurance Report
strWhere = tblInsurance.Product Like "INS %"
stDocName = "rptSalesInsurance"
DoCmd.OpenReport stDocName, acPreview, , strWhere

End Select


Exit_Preview_Report_Click:
Exit Sub

Err_Preview_Report_Click:
MsgBox Err.Description
Resume Exit_Preview_Report_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Try this

Case 2 ' INS - Insurance Report
strWhere = "[Product] Like 'INS *' AND [IssueDate] BETWEEN #" & _
format(Me.StartDate,"mm/dd/yyyy") & "# AND #" &
format(Me.EndDate,"mm/dd/yyyy") & "#"
stDocName = "rptSalesInsurance"
Debug.Print strWhere
DoCmd.OpenReport stDocName, acPreview, , strWhere

HTH Paolo


Chuck said:
I made a couple of changes on the form; i have 2 new text boxes StartDate and
EndDate. I use a Short Date input mask. I have a record with IssueDate
14-sep-2007
If i put in date range 28/9/2007 to 30/10/2007 i dont see the Sep-14 record.
If i put in date range 01/10/2007 to 30/10/2007 i DO see the Sep-14 record.
Why is that. Here is my Case 2 code:

Case 2 ' INS - Insurance Report
strWhere = "[Product] Like 'INS *' AND [IssueDate] BETWEEN #" & _
Me.StartDate & "# AND #" & Me.EndDate & "#"
stDocName = "rptSalesInsurance"
Debug.Print strWhere
DoCmd.OpenReport stDocName, acPreview, , strWhere




Chuck said:
Hi Paolo, [IssueDate] is a date field. The combo box as 4 entries;
FYTD which should equal between Oct 21, 2007 and Oct 20, 2008
2007 which should equal between Jan 1, 2007 and Dec 31, 2007
2008 and so on....
2009 etc

Dont i have to translate the combobox value to an expression and apply to
[IssueDate] so records can be filtered.

Paolo said:
Hi Chuck,

if [IssueDate] contains just the year (I don't think so) do in this way.
if [IssueDate] contains a date in the strwhere strings instead of
tblInsurance.issuedate wirte year(tblInsurance.issuedate)

Private Sub Preview_Report_Click()
On Error GoTo Err_Preview_Report_Click

Dim stDocName As String
Dim strWhere As String

Select Case Me.[ReportType].Value
Case 1 ' Show all Records
'DoCmd.ShowAllRecords
strWhere = "tblInsurance.issuedate=" & "'" & nameoftheyearcombo & "'"
stDocName = "rptSalesInsurance"
DoCmd.OpenReport stDocName, acPreview
Case 2 ' Insurance Report
strWhere = "tblInsurance.Product Like INS* and
tblInsurance.issuedate=" & "'" & nameoftheyearcombo & "'"
stDocName = "rptSalesInsurance"
DoCmd.OpenReport stDocName, acPreview, , strWhere

End Select


Exit_Preview_Report_Click:
Exit Sub

Err_Preview_Report_Click:
MsgBox Err.Description
Resume Exit_Preview_Report_Click

End Sub

HTH Paolo



:

I have a form based on qrySalesReport. The form has an option group
[ReportType] and an unbound combo box, the combo box indicates a year (ie.
2007) and i would like all records from the query to be within 2007
[IssueDate] and when option group 2 is selected, i would like to filter on
field [Product] to only include records where the product name begins with
"INS *" and is wild carded after that. here is the start of my code. thanks
in advance



Private Sub Preview_Report_Click()
On Error GoTo Err_Preview_Report_Click

Dim stDocName As String
Dim strWhere As String

Select Case Me.[ReportType].Value
Case 1 ' Show all Records
'DoCmd.ShowAllRecords
stDocName = "rptSalesInsurance"
DoCmd.OpenReport stDocName, acPreview
Case 2 ' Insurance Report
strWhere = tblInsurance.Product Like "INS %"
stDocName = "rptSalesInsurance"
DoCmd.OpenReport stDocName, acPreview, , strWhere

End Select


Exit_Preview_Report_Click:
Exit Sub

Err_Preview_Report_Click:
MsgBox Err.Description
Resume Exit_Preview_Report_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
As Paolo's suggested, you cannot use dates in dd/mm/yyyy format as-is.

For more on this topic, see what Allen Browne has at
http://www.allenbrowne.com/ser-36.html or what I wrote in my September, 2003
"Access Answers" column in Pinnacle Publication's "Smart Access". You can
download the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Chuck said:
I made a couple of changes on the form; i have 2 new text boxes StartDate
and
EndDate. I use a Short Date input mask. I have a record with IssueDate
14-sep-2007
If i put in date range 28/9/2007 to 30/10/2007 i dont see the Sep-14
record.
If i put in date range 01/10/2007 to 30/10/2007 i DO see the Sep-14
record.
Why is that. Here is my Case 2 code:

Case 2 ' INS - Insurance Report
strWhere = "[Product] Like 'INS *' AND [IssueDate] BETWEEN #" & _
Me.StartDate & "# AND #" & Me.EndDate & "#"
stDocName = "rptSalesInsurance"
Debug.Print strWhere
DoCmd.OpenReport stDocName, acPreview, , strWhere




Chuck said:
Hi Paolo, [IssueDate] is a date field. The combo box as 4 entries;
FYTD which should equal between Oct 21, 2007 and Oct 20, 2008
2007 which should equal between Jan 1, 2007 and Dec 31, 2007
2008 and so on....
2009 etc

Dont i have to translate the combobox value to an expression and apply to
[IssueDate] so records can be filtered.

Paolo said:
Hi Chuck,

if [IssueDate] contains just the year (I don't think so) do in this
way.
if [IssueDate] contains a date in the strwhere strings instead of
tblInsurance.issuedate wirte year(tblInsurance.issuedate)

Private Sub Preview_Report_Click()
On Error GoTo Err_Preview_Report_Click

Dim stDocName As String
Dim strWhere As String

Select Case Me.[ReportType].Value
Case 1 ' Show all Records
'DoCmd.ShowAllRecords
strWhere = "tblInsurance.issuedate=" & "'" &
nameoftheyearcombo & "'"
stDocName = "rptSalesInsurance"
DoCmd.OpenReport stDocName, acPreview
Case 2 ' Insurance Report
strWhere = "tblInsurance.Product Like INS* and
tblInsurance.issuedate=" & "'" & nameoftheyearcombo & "'"
stDocName = "rptSalesInsurance"
DoCmd.OpenReport stDocName, acPreview, , strWhere

End Select


Exit_Preview_Report_Click:
Exit Sub

Err_Preview_Report_Click:
MsgBox Err.Description
Resume Exit_Preview_Report_Click

End Sub

HTH Paolo



:

I have a form based on qrySalesReport. The form has an option group
[ReportType] and an unbound combo box, the combo box indicates a year
(ie.
2007) and i would like all records from the query to be within 2007
[IssueDate] and when option group 2 is selected, i would like to
filter on
field [Product] to only include records where the product name begins
with
"INS *" and is wild carded after that. here is the start of my code.
thanks
in advance



Private Sub Preview_Report_Click()
On Error GoTo Err_Preview_Report_Click

Dim stDocName As String
Dim strWhere As String

Select Case Me.[ReportType].Value
Case 1 ' Show all Records
'DoCmd.ShowAllRecords
stDocName = "rptSalesInsurance"
DoCmd.OpenReport stDocName, acPreview
Case 2 ' Insurance Report
strWhere = tblInsurance.Product Like "INS %"
stDocName = "rptSalesInsurance"
DoCmd.OpenReport stDocName, acPreview, , strWhere

End Select


Exit_Preview_Report_Click:
Exit Sub

Err_Preview_Report_Click:
MsgBox Err.Description
Resume Exit_Preview_Report_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
excellent...thanks

Paolo said:
Try this

Case 2 ' INS - Insurance Report
strWhere = "[Product] Like 'INS *' AND [IssueDate] BETWEEN #" & _
format(Me.StartDate,"mm/dd/yyyy") & "# AND #" &
format(Me.EndDate,"mm/dd/yyyy") & "#"
stDocName = "rptSalesInsurance"
Debug.Print strWhere
DoCmd.OpenReport stDocName, acPreview, , strWhere

HTH Paolo


Chuck said:
I made a couple of changes on the form; i have 2 new text boxes StartDate and
EndDate. I use a Short Date input mask. I have a record with IssueDate
14-sep-2007
If i put in date range 28/9/2007 to 30/10/2007 i dont see the Sep-14 record.
If i put in date range 01/10/2007 to 30/10/2007 i DO see the Sep-14 record.
Why is that. Here is my Case 2 code:

Case 2 ' INS - Insurance Report
strWhere = "[Product] Like 'INS *' AND [IssueDate] BETWEEN #" & _
Me.StartDate & "# AND #" & Me.EndDate & "#"
stDocName = "rptSalesInsurance"
Debug.Print strWhere
DoCmd.OpenReport stDocName, acPreview, , strWhere




Chuck said:
Hi Paolo, [IssueDate] is a date field. The combo box as 4 entries;
FYTD which should equal between Oct 21, 2007 and Oct 20, 2008
2007 which should equal between Jan 1, 2007 and Dec 31, 2007
2008 and so on....
2009 etc

Dont i have to translate the combobox value to an expression and apply to
[IssueDate] so records can be filtered.

:

Hi Chuck,

if [IssueDate] contains just the year (I don't think so) do in this way.
if [IssueDate] contains a date in the strwhere strings instead of
tblInsurance.issuedate wirte year(tblInsurance.issuedate)

Private Sub Preview_Report_Click()
On Error GoTo Err_Preview_Report_Click

Dim stDocName As String
Dim strWhere As String

Select Case Me.[ReportType].Value
Case 1 ' Show all Records
'DoCmd.ShowAllRecords
strWhere = "tblInsurance.issuedate=" & "'" & nameoftheyearcombo & "'"
stDocName = "rptSalesInsurance"
DoCmd.OpenReport stDocName, acPreview
Case 2 ' Insurance Report
strWhere = "tblInsurance.Product Like INS* and
tblInsurance.issuedate=" & "'" & nameoftheyearcombo & "'"
stDocName = "rptSalesInsurance"
DoCmd.OpenReport stDocName, acPreview, , strWhere

End Select


Exit_Preview_Report_Click:
Exit Sub

Err_Preview_Report_Click:
MsgBox Err.Description
Resume Exit_Preview_Report_Click

End Sub

HTH Paolo



:

I have a form based on qrySalesReport. The form has an option group
[ReportType] and an unbound combo box, the combo box indicates a year (ie.
2007) and i would like all records from the query to be within 2007
[IssueDate] and when option group 2 is selected, i would like to filter on
field [Product] to only include records where the product name begins with
"INS *" and is wild carded after that. here is the start of my code. thanks
in advance



Private Sub Preview_Report_Click()
On Error GoTo Err_Preview_Report_Click

Dim stDocName As String
Dim strWhere As String

Select Case Me.[ReportType].Value
Case 1 ' Show all Records
'DoCmd.ShowAllRecords
stDocName = "rptSalesInsurance"
DoCmd.OpenReport stDocName, acPreview
Case 2 ' Insurance Report
strWhere = tblInsurance.Product Like "INS %"
stDocName = "rptSalesInsurance"
DoCmd.OpenReport stDocName, acPreview, , strWhere

End Select


Exit_Preview_Report_Click:
Exit Sub

Err_Preview_Report_Click:
MsgBox Err.Description
Resume Exit_Preview_Report_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Back
Top