Best way to do this (variable report filtering)

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



"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,

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

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

--
Marsh
MVP [MS Access]

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

"Marshall Barton" wrote in message
...
Piperlynne wrote:

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

--
Marsh
MVP [MS Access]

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


--
Duane Hookom
Microsoft Access MVP


"Piperlynne" wrote:

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?

"Steve" schreef in bericht ...
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.

You should have this ...
You should have that... Nonsense !! There is more ways to do this than the simple way you can think of...


But...
You should have left these groups by now,,,

Get lost $teve. Go away... far away....
No-one wants you here... no-one needs you here...

OP look at http://home.tiscali.nl/arracom/whoissteve.html
(Website has been updated and has a new 'look'... we have passed 11.000 pageloads... it's a shame !!)

For those who don't 'agree' with this mail , because $teve was 'helping' with his post...
We warned him a thousand times... Sad, but he is not willing to stop advertising...

He is just toying with these groups... advertising like hell... on and on... for years...
oh yes... and sometimes he answers questions... indeed...
and sometimes good souls here give him credit for that...

== We are totally 'finished' with $teve now...
== Killfile 'StopThisAdvertising' and you won't see these mails....

Arno R

Gina Whipp wrote:

Marshall,

Great minds think alike!



Well, I'll settle for semi-great mind. Allen's the great
mind that put together the article that we think answers the
question.

--
Marsh
MVP [MS Access]

Good Point!

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

"Marshall Barton" wrote in message
...
Gina Whipp wrote:

Marshall,

Great minds think alike!



Well, I'll settle for semi-great mind. Allen's the great
mind that put together the article that we think answers the
question.

--
Marsh
MVP [MS Access]

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?


.