Print preview button and defined filter

[wedd]

I have an interesting challenge I'm trying to do with reporting filters in access 2007 where I'm trying to add a button to an existing form to ultimately show a print preview of a report, but first with a control panel appearing allow a defined filter criteria for

(1) sector;
(2) Between date range;
(3) Core/CBMB/Both;
(4) Introduced to;
(5) Source Individual; and
(6) Source Company

and then adding to an existing report, deals that had been rejected for the last 30 days = ()-30


Thanks and appreciate your help and knowledge...I am a beginner to using this feature, so I really appreciate it

[Imdabaum]

As a suggestion, I would build a form that controls the report. A small pop up modal form is usually what I opt for. You can create several drop down options for the items you would like to filter for.

Once you have the combo boxes set to values, then you can do 1 of 2 things. 1) Modify the base query bound to the report using where clauses and the drop down values or 2) Simply add the where clause in the OpenReport command.

[wedd]

Thanks. Once I've created the print preview button on the form and the control panel allowing a defined filter for the reports. I then have to add to a current report already in the database all the specific deals that have been rejected in the last 30 days = now()-30...Have you any suggestions how this can be done? Thanks

[Imdabaum]

Thanks. Once I've created the print preview button on the form and the control panel allowing a defined filter for the reports. I then have to add to a current report already in the database all the specific deals that have been rejected in the last 30 days = now()-30...Have you any suggestions how this can be done? Thanks

As I mentioned before, once you have the popup form with the drop down boxes to set the filter, then you can have a button on that pop up form with Text: Preview. The button On_Click event would then have approximately the same code.

[VBA]
Sub yourButtonName_On_Click()
Dim strFilter as String

strFilter = "[YOURFIELDNAME] = " & Me.combobox.column(#)
'If your field holds string then add a single quote after the = sign. and put
---> & "'" at the end of the statement.
'If your form has multiple filters, you can repeat the line until you have gathered your requirements
' If Not (IsNull(Me.combobox2)) Then
' strFilter = strFilter & " AND [YOURFIELD2NAME] = " & Me.combobox2.column(#)
DoCmd.OpenReport "YOUR_REPORTNAME", acViewPreview, , strFilter
'You can Press F1 and type DoCmd.OpenReport for more details on this method.

End Sub
[/VBA]

[wedd]

Thanks!

[Imdabaum]

To get the criteria for your dates, just add the criteria in the query bound to your report, or within the filter if you don't want the 30 days as a constant filter.

Between DateAdd("d",-30,Date()) And Date()

Will pull data from 30 days ago until now.
If you just want things 30 days ago, remove the Between and just leave DateAdd("d", -30, Date())

[wedd]

Thanks, and if i wanted to search for specific records 30 days ago...for examples records showing deals that were rejected 30 days ago?

[wedd]

I added this string to a combo list to create the filter...but it never displayed the field names with a column...

Sub Combo82_On_Click()
Dim strFilter As String
If Not (IsNull(Me.combobox2)) Then
strFilter = "[Sector] = " & Me.ComboBox.Column(1)
If Not (IsNull(Me.combobox2)) Then
strFilter = "[Between date range] = " & Me.ComboBox.Column(2)
If Not (IsNull(Me.combobox2)) Then
strFilter = "[Core/CBMB/Both] = " & Me.ComboBox.Column(3)
If Not (IsNull(Me.combobox2)) Then
strFilter = "[Introduced to] = " & Me.ComboBox.Column(4)
If Not (IsNull(Me.combobox2)) Then
strFilter = "[Source Company] = " & Me.ComboBox.Column(5)
If Not (IsNull(Me.combobox2)) Then
strFilter = "[Source Individual] = " & Me.ComboBox.Column(6)





'If your field holds string then add a single quote after the = sign. and put

'If your form has multiple filters, you can repeat the line until you have gathered your requirements
' If Not (IsNull(Me.combobox2)) Then
' strFilter = strFilter & " AND [YOURFIELD2NAME] = " & Me.combobox2.column(#)
DoCmd.OpenReport "DealLogReport-Core", acViewPreview, , strFilter
'You can Press F1 and type DoCmd.OpenReport for more details on this method.

End Sub

[Imdabaum]

You will want to place that code in the button's on_Click event. That way when the report is previewed it gathers the report filters and sends them directly to your OpenReport method.

As a tip, once you enter the code into your button event, highlight the text -->Sub yourbuttonName_On_Click()
Then hit F9. F9 will put a break point so you can test your code. Put your mouse over your combobox.column(#) and identify what the value is. In most cases a dropdown(combo box) will have multiple columns, even if you cannot see all of them. You want to make sure that the column's data aligns with the field value you are trying to filter.

I don't think you'll want to use combobox2.column(1), combobox2.column(2) ,combobox2.column(3) ,and combobox2.column(4) as you have illustrated. combobox2 in the example I gave would only represent the value of one field. You have to identify which field maps to your filter.

I apologize if I seem to push VBA a lot. But it seems the easier way to accomplish this task.