Best way to do this (variable report filtering)

  • Thread starter Thread starter Piperlynne
  • Start date Start date
P

Piperlynne

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?
 
You should have a table for each of the six fields you list (ThemeID,
SponsorID, SiteID, ... etc). Your form should have six comboboxes to select
the value of each field. Then look up QueryDef in the Help file. You will
program the querydef based on the six fields.

Steve
(e-mail address removed)
 
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

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 said:
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?
Click to expand...


You want to build a Where clause (without the word Where)
and use that in the OpenReport method's WhereCondition
argument.

There's a good example of that kind of thing at
http://allenbrowne.com/ser-62.html
 
Marshall,

Great minds think alike!

--
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 said:
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?
Click to expand...


You want to build a Where clause (without the word Where)
and use that in the OpenReport method's WhereCondition
argument.

There's a good example of that kind of thing at
http://allenbrowne.com/ser-62.html
 
I generally use a report selection form that allows users to select a report
from a list box and then either enter or select criteria. For instance, I
will have a couple text boxes "txtStartDate" and "txtEndDate" and
multi-select list boxes like "lboDepts" and "lboEmployees".

My code in the On Click of a command button to open the report might look
something like:

Dim strWhere as String
Dim strRptName as String
strRptName = Me.lboReport
strWhere = "1 = 1 "
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & " AND [DateField]>=#" & _
Me.txtStartDate & "# "
End If
If Not IsNull(Me.txtEndDate) Then
strWhere = strWhere & " AND [DateField]<=#" & _
Me.txtEndDate & "# "
End If
' other code to build more conditions for the strWhere
DoCmd.OpenReport strRptName, acViewPreview, , strWhere
 
Gina said:
Marshall,

Great minds think alike!
Click to expand...


Well, I'll settle for semi-great mind. Allen's the great
mind that put together the article that we think answers the
question.
 
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
 
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

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
 
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 said:
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

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 said:
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

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?


.
Click to expand...

.
Click to expand...
 
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 ([email protected]), 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

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 said:
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

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 said:
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

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?


.
Click to expand...

.
Click to expand...
 
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 said:
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 ([email protected]), 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

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 said:
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

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 said:
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

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?


.
Click to expand...

.
Click to expand...

.
Click to expand...
 
Basically, it is the same with the report name inserted like this
'Author: Allen Browne ([email protected]), 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
Click to expand...


Gina Whipp said:
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 ([email protected]), 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

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 said:
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

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 said:
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

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?


.
Click to expand...

.
Click to expand...

.
Click to expand...
 
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

Basically, it is the same with the report name inserted like this
'Author: Allen Browne ([email protected]), 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
Click to expand...


Gina Whipp said:
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 ([email protected]), 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

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 said:
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

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 said:
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

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?


.
Click to expand...

.
Click to expand...

.
Click to expand...
 
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 ([email protected]), 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 ([email protected]), 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 ([email protected]), June 2006.
 
Back
Top