Best way to do this (variable report filtering)

Piperlynne,

I.m not sure I understand... so let me say it back to you....

You want to remove...

C:\Documents and Settings\barbabik\My Documents\Export ALL.xls"

....and have the End-User select where to place the file?


I could be missing it but where is the line or section of code for the .PDF
report?

You're most welcome, glad part one worked!
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"Piperlynne" wrote in message
...
Ok, so this works THANK YOU!
However, now I'm trying to take out the set destination to have the user
prompted for where to save the report. What do I need to change. (I also
have
another report that is a PDF (Access 2007) and want to do the same thing and
am getting an error that they can't find the report. I checked the spelling
and its correct, but its not working) (2 questions in one there - sorry)

Code:

Private Sub cmdRptALL_Click()
Dim strWhere As String
Dim strReport As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"
strReport = "Export ALL"


If Not IsNull(Me.txtSiteFilter) Then
strWhere = strWhere & "([Site] Like ""*" & Me.txtSiteFilter & "*"") AND "
End If

If Not IsNull(Me.txtAEFilter) Then
strWhere = strWhere & "([AccountExec])Like ""*" & Me.txtAEFilter & "*"")
AND "
End If

If Not IsNull(Me.txtStatusFilter) Then
strWhere = strWhere & "([Status] Like ""*" & Me.txtStatusFilter & "*"")
AND "
End If

If Not IsNull(Me.txtThemeFilter) Then
strWhere = strWhere & "([Theme] Like ""*" & Me.txtThemeFilter & "*"")
AND "
End If

If Not IsNull(Me.txtSpnsrTypeFilter) Then
strWhere = strWhere & "([SponsorshipType]Like ""*" &
Me.txtSpnsrTypeFilter & "*"") AND "
End If

lngLen = Len(strWhere) - 5
If lngLen = 0 Then
MsgBox "No criteria", vbInformation, "Nothing to Do"
Else
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn = True
End If
DoCmd.OutputTo acSendReport, "Export ALL", acFormatXLS, "C:\Documents and
Settings\barbabik\My Documents\Export ALL.xls"


End Sub

"Gina Whipp" wrote:

Piperlynne,

Have a look at...

http://allenbrowne.com/ser-62.html

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"Piperlynne" wrote in message
...
I am trying to create a form that will allow the user to filter the
contents
of a report by up to 6 fields. (Theme, Sponsor, Site, AcctExec,
Advertiser,
Type).
I was leaning on the direction of having Filter On checkboxes that if
checked would point to the fields that the user wanted to filter by. Then
have comboboxes with the values for the filter. If no check boxes checked,
no
filter etc.
I have a query that pulls all information and I have done something
similar
in the past (minus the checkboxes) but my brain isn't working.

Basically the question is. . what is the best way to approach filtering on
0
to many fields.? I'm not extremely vba proficient (I know the very
basics).
Need a little design help please. Basically I'm looking for the
straightest
and simplest line between point a and point b. Ideas?


.

Ok, sorry for the confusion, I have 2 command buttons off the same filter
boxes. I am able to run the reports and select the format and destination now
with this code. However, now I'm not applying the filter to the reports.
Where should I put the strWhere clause in the docmd. Line? Or should I change
my tactic? (So close, yet so far)

The XLS version is:

Private Sub cmdRptALL_Click()
Dim strWhere As String
Dim strReport As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"
strReport = "Export ALL"


If Not IsNull(Me.txtSiteFilter) Then
strWhere = strWhere & "([RptSite] Like ""*" & Me.txtSiteFilter & "*"") AND "
End If

If Not IsNull(Me.txtAEFilter) Then
strWhere = strWhere & "([AccountExec])Like ""*" & Me.txtAEFilter & "*"")
AND "
End If

If Not IsNull(Me.txtStatusFilter) Then
strWhere = strWhere & "([Status] Like ""*" & Me.txtStatusFilter & "*"")
AND "
End If

If Not IsNull(Me.txtThemeFilter) Then
strWhere = strWhere & "([RptTheme] Like ""*" & Me.txtThemeFilter & "*"")
AND "
End If

If Not IsNull(Me.txtSpnsrTypeFilter) Then
strWhere = strWhere & "([SponsorshipType]Like ""*" &
Me.txtSpnsrTypeFilter & "*"") AND "
End If

lngLen = Len(strWhere) - 5
If lngLen = 0 Then
MsgBox "No criteria selected ALL records will be included",
vbInformation, "Nothing to Do"
Else
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn = True
End If
DoCmd.OutputTo acSendReport, "Export ALL", acFormatXLS, , , , ,
acExportQualityPrint


End Sub

and the PDF version is:

Private Sub cmdAERpt_Click()
Dim strWhere As String
Dim strReport As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"
strReport = "Account Exec Report"

If Not IsNull(Me.txtSiteFilter) Then
strWhere = strWhere & "([Site] Like ""*" & Me.txtSiteFilter & "*"") AND "
End If

If Not IsNull(Me.txtAEFilter) Then
strWhere = strWhere & "([AccountExec])Like ""*" & Me.txtAEFilter & "*"")
AND "
End If

If Not IsNull(Me.txtStatusFilter) Then
strWhere = strWhere & "([Status] Like ""*" & Me.txtStatusFilter & "*"")
AND "
End If

If Not IsNull(Me.txtThemeFilter) Then
strWhere = strWhere & "([RptTheme] Like ""*" & Me.txtThemeFilter & "*"")
AND "
End If

If Not IsNull(Me.txtSpnsrTypeFilter) Then
strWhere = strWhere & "([SponsorshipType]Like ""*" &
Me.txtSpnsrTypeFilter & "*"") AND "
End If

lngLen = Len(strWhere) - 5
If lngLen = 0 Then
MsgBox "No criteria selected ALL records will be included",
vbInformation, "Nothing to Do"
Else
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn = True
End If
DoCmd.OutputTo acOutputReport, "Account Exec Report", , , , , ,
acExportQualityPrint

End Sub




"Gina Whipp" wrote:

Piperlynne,

I.m not sure I understand... so let me say it back to you....

You want to remove...

C:\Documents and Settings\barbabik\My Documents\Export ALL.xls"

....and have the End-User select where to place the file?


I could be missing it but where is the line or section of code for the .PDF
report?

You're most welcome, glad part one worked!
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"Piperlynne" wrote in message
...
Ok, so this works THANK YOU!
However, now I'm trying to take out the set destination to have the user
prompted for where to save the report. What do I need to change. (I also
have
another report that is a PDF (Access 2007) and want to do the same thing and
am getting an error that they can't find the report. I checked the spelling
and its correct, but its not working) (2 questions in one there - sorry)

Code:

Private Sub cmdRptALL_Click()
Dim strWhere As String
Dim strReport As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"
strReport = "Export ALL"


If Not IsNull(Me.txtSiteFilter) Then
strWhere = strWhere & "([Site] Like ""*" & Me.txtSiteFilter & "*"") AND "
End If

If Not IsNull(Me.txtAEFilter) Then
strWhere = strWhere & "([AccountExec])Like ""*" & Me.txtAEFilter & "*"")
AND "
End If

If Not IsNull(Me.txtStatusFilter) Then
strWhere = strWhere & "([Status] Like ""*" & Me.txtStatusFilter & "*"")
AND "
End If

If Not IsNull(Me.txtThemeFilter) Then
strWhere = strWhere & "([Theme] Like ""*" & Me.txtThemeFilter & "*"")
AND "
End If

If Not IsNull(Me.txtSpnsrTypeFilter) Then
strWhere = strWhere & "([SponsorshipType]Like ""*" &
Me.txtSpnsrTypeFilter & "*"") AND "
End If

lngLen = Len(strWhere) - 5
If lngLen = 0 Then
MsgBox "No criteria", vbInformation, "Nothing to Do"
Else
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn = True
End If
DoCmd.OutputTo acSendReport, "Export ALL", acFormatXLS, "C:\Documents and
Settings\barbabik\My Documents\Export ALL.xls"


End Sub

"Gina Whipp" wrote:

Piperlynne,

Have a look at...

http://allenbrowne.com/ser-62.html

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"Piperlynne" wrote in message
...
I am trying to create a form that will allow the user to filter the
contents
of a report by up to 6 fields. (Theme, Sponsor, Site, AcctExec,
Advertiser,
Type).
I was leaning on the direction of having Filter On checkboxes that if
checked would point to the fields that the user wanted to filter by. Then
have comboboxes with the values for the filter. If no check boxes checked,
no
filter etc.
I have a query that pulls all information and I have done something
similar
in the past (minus the checkboxes) but my brain isn't working.

Basically the question is. . what is the best way to approach filtering on
0
to many fields.? I'm not extremely vba proficient (I know the very
basics).
Need a little design help please. Basically I'm looking for the
straightest
and simplest line between point a and point b. Ideas?


.


.

Piperlynne,

Tw 0things... number one... Please make sure you include the AUthor's name
when available when copying code into your module or pasting it here in the
newsgroup. It's just the polite thing to do...

Second item... Your going to need to apply the strWhere to your report, see
below...

You're going to need something like...

'Author: Allen Browne ), June 2006.


lngLen = Len(strWhere) - 5
If lngLen = 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to
Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Reports![YourReportName].Filter = strWhere
Reports![YourReportName].FilterOn = True
End If

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"Piperlynne" wrote in message
...
Ok, sorry for the confusion, I have 2 command buttons off the same filter
boxes. I am able to run the reports and select the format and destination
now
with this code. However, now I'm not applying the filter to the reports.
Where should I put the strWhere clause in the docmd. Line? Or should I
change
my tactic? (So close, yet so far)

The XLS version is:

Private Sub cmdRptALL_Click()
Dim strWhere As String
Dim strReport As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"
strReport = "Export ALL"


If Not IsNull(Me.txtSiteFilter) Then
strWhere = strWhere & "([RptSite] Like ""*" & Me.txtSiteFilter & "*"") AND
"
End If

If Not IsNull(Me.txtAEFilter) Then
strWhere = strWhere & "([AccountExec])Like ""*" & Me.txtAEFilter & "*"")
AND "
End If

If Not IsNull(Me.txtStatusFilter) Then
strWhere = strWhere & "([Status] Like ""*" & Me.txtStatusFilter & "*"")
AND "
End If

If Not IsNull(Me.txtThemeFilter) Then
strWhere = strWhere & "([RptTheme] Like ""*" & Me.txtThemeFilter & "*"")
AND "
End If

If Not IsNull(Me.txtSpnsrTypeFilter) Then
strWhere = strWhere & "([SponsorshipType]Like ""*" &
Me.txtSpnsrTypeFilter & "*"") AND "
End If

lngLen = Len(strWhere) - 5
If lngLen = 0 Then
MsgBox "No criteria selected ALL records will be included",
vbInformation, "Nothing to Do"
Else
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn = True
End If
DoCmd.OutputTo acSendReport, "Export ALL", acFormatXLS, , , , ,
acExportQualityPrint


End Sub

and the PDF version is:

Private Sub cmdAERpt_Click()
Dim strWhere As String
Dim strReport As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"
strReport = "Account Exec Report"

If Not IsNull(Me.txtSiteFilter) Then
strWhere = strWhere & "([Site] Like ""*" & Me.txtSiteFilter & "*"") AND "
End If

If Not IsNull(Me.txtAEFilter) Then
strWhere = strWhere & "([AccountExec])Like ""*" & Me.txtAEFilter & "*"")
AND "
End If

If Not IsNull(Me.txtStatusFilter) Then
strWhere = strWhere & "([Status] Like ""*" & Me.txtStatusFilter & "*"")
AND "
End If

If Not IsNull(Me.txtThemeFilter) Then
strWhere = strWhere & "([RptTheme] Like ""*" & Me.txtThemeFilter & "*"")
AND "
End If

If Not IsNull(Me.txtSpnsrTypeFilter) Then
strWhere = strWhere & "([SponsorshipType]Like ""*" &
Me.txtSpnsrTypeFilter & "*"") AND "
End If

lngLen = Len(strWhere) - 5
If lngLen = 0 Then
MsgBox "No criteria selected ALL records will be included",
vbInformation, "Nothing to Do"
Else
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn = True
End If
DoCmd.OutputTo acOutputReport, "Account Exec Report", , , , , ,
acExportQualityPrint

End Sub




"Gina Whipp" wrote:

Piperlynne,

I.m not sure I understand... so let me say it back to you....

You want to remove...

C:\Documents and Settings\barbabik\My Documents\Export ALL.xls"

....and have the End-User select where to place the file?


I could be missing it but where is the line or section of code for the
.PDF
report?

You're most welcome, glad part one worked!
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"Piperlynne" wrote in message
...
Ok, so this works THANK YOU!
However, now I'm trying to take out the set destination to have the user
prompted for where to save the report. What do I need to change. (I also
have
another report that is a PDF (Access 2007) and want to do the same thing
and
am getting an error that they can't find the report. I checked the
spelling
and its correct, but its not working) (2 questions in one there - sorry)

Code:

Private Sub cmdRptALL_Click()
Dim strWhere As String
Dim strReport As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"
strReport = "Export ALL"


If Not IsNull(Me.txtSiteFilter) Then
strWhere = strWhere & "([Site] Like ""*" & Me.txtSiteFilter & "*"") AND
"
End If

If Not IsNull(Me.txtAEFilter) Then
strWhere = strWhere & "([AccountExec])Like ""*" & Me.txtAEFilter &
"*"")
AND "
End If

If Not IsNull(Me.txtStatusFilter) Then
strWhere = strWhere & "([Status] Like ""*" & Me.txtStatusFilter &
"*"")
AND "
End If

If Not IsNull(Me.txtThemeFilter) Then
strWhere = strWhere & "([Theme] Like ""*" & Me.txtThemeFilter & "*"")
AND "
End If

If Not IsNull(Me.txtSpnsrTypeFilter) Then
strWhere = strWhere & "([SponsorshipType]Like ""*" &
Me.txtSpnsrTypeFilter & "*"") AND "
End If

lngLen = Len(strWhere) - 5
If lngLen = 0 Then
MsgBox "No criteria", vbInformation, "Nothing to Do"
Else
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn = True
End If
DoCmd.OutputTo acSendReport, "Export ALL", acFormatXLS, "C:\Documents and
Settings\barbabik\My Documents\Export ALL.xls"


End Sub

"Gina Whipp" wrote:

Piperlynne,

Have a look at...

http://allenbrowne.com/ser-62.html

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"Piperlynne" wrote in message
...
I am trying to create a form that will allow the user to filter the
contents
of a report by up to 6 fields. (Theme, Sponsor, Site, AcctExec,
Advertiser,
Type).
I was leaning on the direction of having Filter On checkboxes that if
checked would point to the fields that the user wanted to filter by.
Then
have comboboxes with the values for the filter. If no check boxes
checked,
no
filter etc.
I have a query that pulls all information and I have done something
similar
in the past (minus the checkboxes) but my brain isn't working.

Basically the question is. . what is the best way to approach filtering
on
0
to many fields.? I'm not extremely vba proficient (I know the very
basics).
Need a little design help please. Basically I'm looking for the
straightest
and simplest line between point a and point b. Ideas?


.


.

Hi Gina,

You're right and of course I attributed it to the brilliant Allen Browne in
the code, I just didn't include in the msg as I thought it was clear from the
string. My mistake and apologies.

I tried this and its telling me the report doesn't exist. When I take out
the Reports![Export ALL]. and replace it with Me. it doesn't send and error
(and doesn't filter - which is my problem).
It's picking up the report name in the DoCmd etc, but not there. Ideas?

"Gina Whipp" wrote:

Piperlynne,

Tw 0things... number one... Please make sure you include the AUthor's name
when available when copying code into your module or pasting it here in the
newsgroup. It's just the polite thing to do...

Second item... Your going to need to apply the strWhere to your report, see
below...

You're going to need something like...

'Author: Allen Browne ), June 2006.


lngLen = Len(strWhere) - 5
If lngLen = 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to
Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Reports![YourReportName].Filter = strWhere
Reports![YourReportName].FilterOn = True
End If

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"Piperlynne" wrote in message
...
Ok, sorry for the confusion, I have 2 command buttons off the same filter
boxes. I am able to run the reports and select the format and destination
now
with this code. However, now I'm not applying the filter to the reports.
Where should I put the strWhere clause in the docmd. Line? Or should I
change
my tactic? (So close, yet so far)

The XLS version is:

Private Sub cmdRptALL_Click()
Dim strWhere As String
Dim strReport As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"
strReport = "Export ALL"


If Not IsNull(Me.txtSiteFilter) Then
strWhere = strWhere & "([RptSite] Like ""*" & Me.txtSiteFilter & "*"") AND
"
End If

If Not IsNull(Me.txtAEFilter) Then
strWhere = strWhere & "([AccountExec])Like ""*" & Me.txtAEFilter & "*"")
AND "
End If

If Not IsNull(Me.txtStatusFilter) Then
strWhere = strWhere & "([Status] Like ""*" & Me.txtStatusFilter & "*"")
AND "
End If

If Not IsNull(Me.txtThemeFilter) Then
strWhere = strWhere & "([RptTheme] Like ""*" & Me.txtThemeFilter & "*"")
AND "
End If

If Not IsNull(Me.txtSpnsrTypeFilter) Then
strWhere = strWhere & "([SponsorshipType]Like ""*" &
Me.txtSpnsrTypeFilter & "*"") AND "
End If

lngLen = Len(strWhere) - 5
If lngLen = 0 Then
MsgBox "No criteria selected ALL records will be included",
vbInformation, "Nothing to Do"
Else
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn = True
End If
DoCmd.OutputTo acSendReport, "Export ALL", acFormatXLS, , , , ,
acExportQualityPrint


End Sub

and the PDF version is:

Private Sub cmdAERpt_Click()
Dim strWhere As String
Dim strReport As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"
strReport = "Account Exec Report"

If Not IsNull(Me.txtSiteFilter) Then
strWhere = strWhere & "([Site] Like ""*" & Me.txtSiteFilter & "*"") AND "
End If

If Not IsNull(Me.txtAEFilter) Then
strWhere = strWhere & "([AccountExec])Like ""*" & Me.txtAEFilter & "*"")
AND "
End If

If Not IsNull(Me.txtStatusFilter) Then
strWhere = strWhere & "([Status] Like ""*" & Me.txtStatusFilter & "*"")
AND "
End If

If Not IsNull(Me.txtThemeFilter) Then
strWhere = strWhere & "([RptTheme] Like ""*" & Me.txtThemeFilter & "*"")
AND "
End If

If Not IsNull(Me.txtSpnsrTypeFilter) Then
strWhere = strWhere & "([SponsorshipType]Like ""*" &
Me.txtSpnsrTypeFilter & "*"") AND "
End If

lngLen = Len(strWhere) - 5
If lngLen = 0 Then
MsgBox "No criteria selected ALL records will be included",
vbInformation, "Nothing to Do"
Else
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn = True
End If
DoCmd.OutputTo acOutputReport, "Account Exec Report", , , , , ,
acExportQualityPrint

End Sub




"Gina Whipp" wrote:

Piperlynne,

I.m not sure I understand... so let me say it back to you....

You want to remove...

C:\Documents and Settings\barbabik\My Documents\Export ALL.xls"

....and have the End-User select where to place the file?


I could be missing it but where is the line or section of code for the
.PDF
report?

You're most welcome, glad part one worked!
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"Piperlynne" wrote in message
...
Ok, so this works THANK YOU!
However, now I'm trying to take out the set destination to have the user
prompted for where to save the report. What do I need to change. (I also
have
another report that is a PDF (Access 2007) and want to do the same thing
and
am getting an error that they can't find the report. I checked the
spelling
and its correct, but its not working) (2 questions in one there - sorry)

Code:

Private Sub cmdRptALL_Click()
Dim strWhere As String
Dim strReport As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"
strReport = "Export ALL"


If Not IsNull(Me.txtSiteFilter) Then
strWhere = strWhere & "([Site] Like ""*" & Me.txtSiteFilter & "*"") AND
"
End If

If Not IsNull(Me.txtAEFilter) Then
strWhere = strWhere & "([AccountExec])Like ""*" & Me.txtAEFilter &
"*"")
AND "
End If

If Not IsNull(Me.txtStatusFilter) Then
strWhere = strWhere & "([Status] Like ""*" & Me.txtStatusFilter &
"*"")
AND "
End If

If Not IsNull(Me.txtThemeFilter) Then
strWhere = strWhere & "([Theme] Like ""*" & Me.txtThemeFilter & "*"")
AND "
End If

If Not IsNull(Me.txtSpnsrTypeFilter) Then
strWhere = strWhere & "([SponsorshipType]Like ""*" &
Me.txtSpnsrTypeFilter & "*"") AND "
End If

lngLen = Len(strWhere) - 5
If lngLen = 0 Then
MsgBox "No criteria", vbInformation, "Nothing to Do"
Else
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn = True
End If
DoCmd.OutputTo acSendReport, "Export ALL", acFormatXLS, "C:\Documents and
Settings\barbabik\My Documents\Export ALL.xls"


End Sub

"Gina Whipp" wrote:

Piperlynne,

Have a look at...

http://allenbrowne.com/ser-62.html

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"Piperlynne" wrote in message
...
I am trying to create a form that will allow the user to filter the
contents
of a report by up to 6 fields. (Theme, Sponsor, Site, AcctExec,
Advertiser,
Type).
I was leaning on the direction of having Filter On checkboxes that if
checked would point to the fields that the user wanted to filter by.
Then
have comboboxes with the values for the filter. If no check boxes
checked,
no
filter etc.
I have a query that pulls all information and I have done something
similar
in the past (minus the checkboxes) but my brain isn't working.

Basically the question is. . what is the best way to approach filtering
on
0
to many fields.? I'm not extremely vba proficient (I know the very
basics).
Need a little design help please. Basically I'm looking for the
straightest
and simplest line between point a and point b. Ideas?


.


.


.

Basically, it is the same with the report name inserted like this

'Author: Allen Browne ), June 2006.


lngLen = Len(strWhere) - 5
If lngLen = 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to
Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Reports![Export ALL].Filter = strWhere
Reports![Export ALL].FilterOn = True
End If



"Gina Whipp" wrote:

Piperlynne,

Tw 0things... number one... Please make sure you include the AUthor's name
when available when copying code into your module or pasting it here in the
newsgroup. It's just the polite thing to do...

Second item... Your going to need to apply the strWhere to your report, see
below...

You're going to need something like...

'Author: Allen Browne ), June 2006.


lngLen = Len(strWhere) - 5
If lngLen = 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to
Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Reports![YourReportName].Filter = strWhere
Reports![YourReportName].FilterOn = True
End If

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"Piperlynne" wrote in message
...
Ok, sorry for the confusion, I have 2 command buttons off the same filter
boxes. I am able to run the reports and select the format and destination
now
with this code. However, now I'm not applying the filter to the reports.
Where should I put the strWhere clause in the docmd. Line? Or should I
change
my tactic? (So close, yet so far)

The XLS version is:

Private Sub cmdRptALL_Click()
Dim strWhere As String
Dim strReport As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"
strReport = "Export ALL"


If Not IsNull(Me.txtSiteFilter) Then
strWhere = strWhere & "([RptSite] Like ""*" & Me.txtSiteFilter & "*"") AND
"
End If

If Not IsNull(Me.txtAEFilter) Then
strWhere = strWhere & "([AccountExec])Like ""*" & Me.txtAEFilter & "*"")
AND "
End If

If Not IsNull(Me.txtStatusFilter) Then
strWhere = strWhere & "([Status] Like ""*" & Me.txtStatusFilter & "*"")
AND "
End If

If Not IsNull(Me.txtThemeFilter) Then
strWhere = strWhere & "([RptTheme] Like ""*" & Me.txtThemeFilter & "*"")
AND "
End If

If Not IsNull(Me.txtSpnsrTypeFilter) Then
strWhere = strWhere & "([SponsorshipType]Like ""*" &
Me.txtSpnsrTypeFilter & "*"") AND "
End If

lngLen = Len(strWhere) - 5
If lngLen = 0 Then
MsgBox "No criteria selected ALL records will be included",
vbInformation, "Nothing to Do"
Else
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn = True
End If
DoCmd.OutputTo acSendReport, "Export ALL", acFormatXLS, , , , ,
acExportQualityPrint


End Sub

and the PDF version is:

Private Sub cmdAERpt_Click()
Dim strWhere As String
Dim strReport As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"
strReport = "Account Exec Report"

If Not IsNull(Me.txtSiteFilter) Then
strWhere = strWhere & "([Site] Like ""*" & Me.txtSiteFilter & "*"") AND "
End If

If Not IsNull(Me.txtAEFilter) Then
strWhere = strWhere & "([AccountExec])Like ""*" & Me.txtAEFilter & "*"")
AND "
End If

If Not IsNull(Me.txtStatusFilter) Then
strWhere = strWhere & "([Status] Like ""*" & Me.txtStatusFilter & "*"")
AND "
End If

If Not IsNull(Me.txtThemeFilter) Then
strWhere = strWhere & "([RptTheme] Like ""*" & Me.txtThemeFilter & "*"")
AND "
End If

If Not IsNull(Me.txtSpnsrTypeFilter) Then
strWhere = strWhere & "([SponsorshipType]Like ""*" &
Me.txtSpnsrTypeFilter & "*"") AND "
End If

lngLen = Len(strWhere) - 5
If lngLen = 0 Then
MsgBox "No criteria selected ALL records will be included",
vbInformation, "Nothing to Do"
Else
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn = True
End If
DoCmd.OutputTo acOutputReport, "Account Exec Report", , , , , ,
acExportQualityPrint

End Sub




"Gina Whipp" wrote:

Piperlynne,

I.m not sure I understand... so let me say it back to you....

You want to remove...

C:\Documents and Settings\barbabik\My Documents\Export ALL.xls"

....and have the End-User select where to place the file?


I could be missing it but where is the line or section of code for the
.PDF
report?

You're most welcome, glad part one worked!
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"Piperlynne" wrote in message
...
Ok, so this works THANK YOU!
However, now I'm trying to take out the set destination to have the user
prompted for where to save the report. What do I need to change. (I also
have
another report that is a PDF (Access 2007) and want to do the same thing
and
am getting an error that they can't find the report. I checked the
spelling
and its correct, but its not working) (2 questions in one there - sorry)

Code:

Private Sub cmdRptALL_Click()
Dim strWhere As String
Dim strReport As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"
strReport = "Export ALL"


If Not IsNull(Me.txtSiteFilter) Then
strWhere = strWhere & "([Site] Like ""*" & Me.txtSiteFilter & "*"") AND
"
End If

If Not IsNull(Me.txtAEFilter) Then
strWhere = strWhere & "([AccountExec])Like ""*" & Me.txtAEFilter &
"*"")
AND "
End If

If Not IsNull(Me.txtStatusFilter) Then
strWhere = strWhere & "([Status] Like ""*" & Me.txtStatusFilter &
"*"")
AND "
End If

If Not IsNull(Me.txtThemeFilter) Then
strWhere = strWhere & "([Theme] Like ""*" & Me.txtThemeFilter & "*"")
AND "
End If

If Not IsNull(Me.txtSpnsrTypeFilter) Then
strWhere = strWhere & "([SponsorshipType]Like ""*" &
Me.txtSpnsrTypeFilter & "*"") AND "
End If

lngLen = Len(strWhere) - 5
If lngLen = 0 Then
MsgBox "No criteria", vbInformation, "Nothing to Do"
Else
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn = True
End If
DoCmd.OutputTo acSendReport, "Export ALL", acFormatXLS, "C:\Documents and
Settings\barbabik\My Documents\Export ALL.xls"


End Sub

"Gina Whipp" wrote:

Piperlynne,

Have a look at...

http://allenbrowne.com/ser-62.html

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"Piperlynne" wrote in message
...
I am trying to create a form that will allow the user to filter the
contents
of a report by up to 6 fields. (Theme, Sponsor, Site, AcctExec,
Advertiser,
Type).
I was leaning on the direction of having Filter On checkboxes that if
checked would point to the fields that the user wanted to filter by.
Then
have comboboxes with the values for the filter. If no check boxes
checked,
no
filter etc.
I have a query that pulls all information and I have done something
similar
in the past (minus the checkboxes) but my brain isn't working.

Basically the question is. . what is the best way to approach filtering
on
0
to many fields.? I'm not extremely vba proficient (I know the very
basics).
Need a little design help please. Basically I'm looking for the
straightest
and simplest line between point a and point b. Ideas?


.


.


.

Piperlynne,

Did you remove the strReport = "Export ALL"? That might be the problem.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"Piperlynne" wrote in message
...
Basically, it is the same with the report name inserted like this

'Author: Allen Browne ), June 2006.


lngLen = Len(strWhere) - 5
If lngLen = 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the
"
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints
to
Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Reports![Export ALL].Filter = strWhere
Reports![Export ALL].FilterOn = True
End If



"Gina Whipp" wrote:

Piperlynne,

Tw 0things... number one... Please make sure you include the AUthor's
name
when available when copying code into your module or pasting it here in
the
newsgroup. It's just the polite thing to do...

Second item... Your going to need to apply the strWhere to your report,
see
below...

You're going to need something like...

'Author: Allen Browne ), June 2006.


lngLen = Len(strWhere) - 5
If lngLen = 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the
"
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints
to
Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Reports![YourReportName].Filter = strWhere
Reports![YourReportName].FilterOn = True
End If

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"Piperlynne" wrote in message
...
Ok, sorry for the confusion, I have 2 command buttons off the same filter
boxes. I am able to run the reports and select the format and destination
now
with this code. However, now I'm not applying the filter to the reports.
Where should I put the strWhere clause in the docmd. Line? Or should I
change
my tactic? (So close, yet so far)

The XLS version is:

Private Sub cmdRptALL_Click()
Dim strWhere As String
Dim strReport As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"
strReport = "Export ALL"


If Not IsNull(Me.txtSiteFilter) Then
strWhere = strWhere & "([RptSite] Like ""*" & Me.txtSiteFilter & "*"")
AND
"
End If

If Not IsNull(Me.txtAEFilter) Then
strWhere = strWhere & "([AccountExec])Like ""*" & Me.txtAEFilter &
"*"")
AND "
End If

If Not IsNull(Me.txtStatusFilter) Then
strWhere = strWhere & "([Status] Like ""*" & Me.txtStatusFilter &
"*"")
AND "
End If

If Not IsNull(Me.txtThemeFilter) Then
strWhere = strWhere & "([RptTheme] Like ""*" & Me.txtThemeFilter &
"*"")
AND "
End If

If Not IsNull(Me.txtSpnsrTypeFilter) Then
strWhere = strWhere & "([SponsorshipType]Like ""*" &
Me.txtSpnsrTypeFilter & "*"") AND "
End If

lngLen = Len(strWhere) - 5
If lngLen = 0 Then
MsgBox "No criteria selected ALL records will be included",
vbInformation, "Nothing to Do"
Else
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn = True
End If
DoCmd.OutputTo acSendReport, "Export ALL", acFormatXLS, , , , ,
acExportQualityPrint


End Sub

and the PDF version is:

Private Sub cmdAERpt_Click()
Dim strWhere As String
Dim strReport As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"
strReport = "Account Exec Report"

If Not IsNull(Me.txtSiteFilter) Then
strWhere = strWhere & "([Site] Like ""*" & Me.txtSiteFilter & "*"") AND
"
End If

If Not IsNull(Me.txtAEFilter) Then
strWhere = strWhere & "([AccountExec])Like ""*" & Me.txtAEFilter &
"*"")
AND "
End If

If Not IsNull(Me.txtStatusFilter) Then
strWhere = strWhere & "([Status] Like ""*" & Me.txtStatusFilter &
"*"")
AND "
End If

If Not IsNull(Me.txtThemeFilter) Then
strWhere = strWhere & "([RptTheme] Like ""*" & Me.txtThemeFilter &
"*"")
AND "
End If

If Not IsNull(Me.txtSpnsrTypeFilter) Then
strWhere = strWhere & "([SponsorshipType]Like ""*" &
Me.txtSpnsrTypeFilter & "*"") AND "
End If

lngLen = Len(strWhere) - 5
If lngLen = 0 Then
MsgBox "No criteria selected ALL records will be included",
vbInformation, "Nothing to Do"
Else
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn = True
End If
DoCmd.OutputTo acOutputReport, "Account Exec Report", , , , , ,
acExportQualityPrint

End Sub




"Gina Whipp" wrote:

Piperlynne,

I.m not sure I understand... so let me say it back to you....

You want to remove...

C:\Documents and Settings\barbabik\My Documents\Export ALL.xls"

....and have the End-User select where to place the file?


I could be missing it but where is the line or section of code for the
.PDF
report?

You're most welcome, glad part one worked!
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"Piperlynne" wrote in message
...
Ok, so this works THANK YOU!
However, now I'm trying to take out the set destination to have the user
prompted for where to save the report. What do I need to change. (I also
have
another report that is a PDF (Access 2007) and want to do the same thing
and
am getting an error that they can't find the report. I checked the
spelling
and its correct, but its not working) (2 questions in one there - sorry)

Code:

Private Sub cmdRptALL_Click()
Dim strWhere As String
Dim strReport As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"
strReport = "Export ALL"


If Not IsNull(Me.txtSiteFilter) Then
strWhere = strWhere & "([Site] Like ""*" & Me.txtSiteFilter & "*"")
AND
"
End If

If Not IsNull(Me.txtAEFilter) Then
strWhere = strWhere & "([AccountExec])Like ""*" & Me.txtAEFilter &
"*"")
AND "
End If

If Not IsNull(Me.txtStatusFilter) Then
strWhere = strWhere & "([Status] Like ""*" & Me.txtStatusFilter &
"*"")
AND "
End If

If Not IsNull(Me.txtThemeFilter) Then
strWhere = strWhere & "([Theme] Like ""*" & Me.txtThemeFilter &
"*"")
AND "
End If

If Not IsNull(Me.txtSpnsrTypeFilter) Then
strWhere = strWhere & "([SponsorshipType]Like ""*" &
Me.txtSpnsrTypeFilter & "*"") AND "
End If

lngLen = Len(strWhere) - 5
If lngLen = 0 Then
MsgBox "No criteria", vbInformation, "Nothing to Do"
Else
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn = True
End If
DoCmd.OutputTo acSendReport, "Export ALL", acFormatXLS, "C:\Documents
and
Settings\barbabik\My Documents\Export ALL.xls"


End Sub

"Gina Whipp" wrote:

Piperlynne,

Have a look at...

http://allenbrowne.com/ser-62.html

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"Piperlynne" wrote in message
...
I am trying to create a form that will allow the user to filter the
contents
of a report by up to 6 fields. (Theme, Sponsor, Site, AcctExec,
Advertiser,
Type).
I was leaning on the direction of having Filter On checkboxes that if
checked would point to the fields that the user wanted to filter by.
Then
have comboboxes with the values for the filter. If no check boxes
checked,
no
filter etc.
I have a query that pulls all information and I have done something
similar
in the past (minus the checkboxes) but my brain isn't working.

Basically the question is. . what is the best way to approach
filtering
on
0
to many fields.? I'm not extremely vba proficient (I know the very
basics).
Need a little design help please. Basically I'm looking for the
straightest
and simplest line between point a and point b. Ideas?


.


.


.

THANK YOU.
Ok new problem. LOL
Using Allen Browne's code with a few tweaks, and I got it to work on click.
But now its acting strangely. I'm getting an "Access has encountered an error
and needs to close - repair" message on click now. . . sometimes. . .if I
open the form in design mode and then go back to form mode and click the
button it works. ODD.
I have the database set to compact and repair on close. . .
Should I removed the strReport As String and strReport ="Report Name" pieces
since they don't seem to really be doing anything?
Code as it appears(2 buttons - same idea - error occuring on both buttons):

'Author: Allen Browne ), June 2006.
Private Sub cmdAERpt_Click()
Dim strWhere As String
Dim strReport As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"
strReport = "Account Exec Report"

If Not IsNull(Me.txtSiteFilter) Then
strWhere = strWhere & "([Site] Like ""*" & Me.txtSiteFilter & "*"") AND "
End If

If Not IsNull(Me.txtAEFilter) Then
strWhere = strWhere & "([AccountExec]Like ""*" & Me.txtAEFilter & "*"")
AND "
End If

If Not IsNull(Me.txtStatusFilter) Then
strWhere = strWhere & "([Status] Like ""*" & Me.txtStatusFilter & "*"")
AND "
End If

lngLen = Len(strWhere) - 5
If lngLen = 0 Then
MsgBox "No criteria selected ALL records will be included",
vbInformation, "Nothing to Do"
Else
DoCmd.OpenReport "Account Exec Report", acViewPreview, strWhere
strWhere = Left$(strWhere, lngLen)
[Reports]![Account Exec Report].Filter = strWhere
[Reports]![Account Exec Report].FilterOn = True
End If
DoCmd.OutputTo acOutputReport, "Account Exec Report", , , , , ,
acExportQualityPrint

End Sub

'Author: Allen Browne ), June 2006.
Private Sub cmdRptALL_Click()
Dim strWhere As String
Dim strReport As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"
strReport = "Export ALL"


If Not IsNull(Me.txtSiteFilter) Then
strWhere = strWhere & "([RptSite] Like ""*" & Me.txtSiteFilter & "*"") AND "
End If

If Not IsNull(Me.txtAEFilter) Then
strWhere = strWhere & "([AccountExec] Like ""*" & Me.txtAEFilter & "*"")
AND "
End If

If Not IsNull(Me.txtStatusFilter) Then
strWhere = strWhere & "([Status] Like ""*" & Me.txtStatusFilter & "*"")
AND "
End If

If Not IsNull(Me.txtThemeFilter) Then
strWhere = strWhere & "([RptTheme] Like ""*" & Me.txtThemeFilter & "*"")
AND "
End If

If Not IsNull(Me.txtSpnsrTypeFilter) Then
strWhere = strWhere & "([SponsorshipType]Like ""*" &
Me.txtSpnsrTypeFilter & "*"") AND "
End If

lngLen = Len(strWhere) - 5
If lngLen = 0 Then
MsgBox "No criteria selected ALL records will be included",
vbInformation, "Nothing to Do"
Else
DoCmd.OpenReport "Export ALL", acViewPreview, strWhere
strWhere = Left$(strWhere, lngLen)
[Reports]![Export ALL].Filter = strWhere
[Reports]![Export ALL].FilterOn = True
End If
DoCmd.OutputTo acSendReport, "Export ALL", acFormatXLS, , , , ,
acExportQualityPrint
End Sub

'Author: Allen Browne ), June 2006.