ACCESS FEED: RE: [MS_AccessPros] Report Criteria

Toukey-

OK, the light goes on! Yes, you want a dynamic filter.

For a simple single selection (not multiple insurers or multiple claim
statuses), you need three combo boxes:

1) Insurer - a list of insurers

2) Claim Status - a list of the different statuses

3) Type of Claim - a list of the different types

Behind a command button, examine each combo in turn and build a filter. Then
open the report using that filter.

Dim strWhere As String

If Not IsNull(Me.cmbInsurer) Then

strWhere = "(Insurer = " & Me.cmbInsurer & ")"

End If

If Not IsNull(Me.cmbClaimStatus) Then

If Len(strWhere) <> 0 Then

strWhere = strWhere & " AND "

End If

strWhere = strWhere & "(ClaimStatus = " & Me.cmbClaimStatus & ")"

End If

If Not IsNull(Me.cmbTypeOfClaim) Then

If Len(strWhere) <> 0 Then

strWhere = strWhere & " AND "

End If

strWhere = strWhere & "(TypeOfClaim = " & Me.cmbTypeOfClaim & ")"

End If

DoCmd.OpenReport "rptInsurance", acViewPreview, _

WhereCondition:=strWhere

You'll have to adjust the code for names of your controls, names of your fields,
and the data type of the fields. For example, if Type Of Claim is a string,
then you'll need to put quotes around the value from the combo box.

John Viescas, author

Microsoft Office Access 2010 Inside Out

Microsoft Office Access 2007 Inside Out

Building Microsoft Access Applications

Microsoft Office Access 2003 Inside Out

SQL Queries for Mere Mortals

<http://www.viescas.com/> http://www.viescas.com/

(Paris, France)

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of toukey1
Sent: Sunday, May 20, 2012 10:43 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Report Criteria

ok John. Let me see how best I can explain this.

The report comprises of the following fields:

date of loss, insured name, type of loss, comments, claim status, type of claim,
due date, insurer

The user wants to be able to run the report based on the one or more of the
following (as mentioned before):

- Insurer
- Claim Status
- Type of Claim

So, the user can run the report
by selecting Insurer "A" where the Claim Status is "Paid"
or the user can run the report for only Insuer "A"
or the user can run the report where the Insurer is "A", the Claim Status is
"Paid" and the Type of Claim is "Motor"

Hope I have made it a little clearer for you.

Regards
Toukey

--- In MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> , John Viescas <JohnV@...>
wrote:
>
> Toukey-
>
>
>
> You guess? You didn't tell me what's the difference. What do you want to see
> when Insurer or Claim Status or Type Of Claim is selected?
>
>
>
> John Viescas, author
>
> Microsoft Office Access 2010 Inside Out
>
> Microsoft Office Access 2007 Inside Out
>
> Building Microsoft Access Applications
>
> Microsoft Office Access 2003 Inside Out
>
> SQL Queries for Mere Mortals
>
> <http://www.viescas.com/> http://www.viescas.com/
>
> (Paris, France)
>
>
>
>
>
>
>
> From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> [mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of toukey1
> Sent: Sunday, May 20, 2012 10:13 PM
> To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> Subject: Re: [MS_AccessPros] Report Criteria
>
>
>
>
>
> John,
>
> I have the report based on a query so I guess the options will be used as
> filters.
>
> Toukey
>
> --- In MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com> , John Viescas <JohnV@>
> wrote:
> >
> > Toukey-
> >
> >
> >
> > What's the difference between the reports? A filter?
> >
> >
> >
> > John Viescas, author
> >
> > Microsoft Office Access 2010 Inside Out
> >
> > Microsoft Office Access 2007 Inside Out
> >
> > Building Microsoft Access Applications
> >
> > Microsoft Office Access 2003 Inside Out
> >
> > SQL Queries for Mere Mortals
> >
> > <http://www.viescas.com/> http://www.viescas.com/
> >
> > (Paris, France)
> >
> >
> >
> >
> >
> >
> >
> > From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > [mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of toukey1
> > Sent: Sunday, May 20, 2012 10:01 PM
> > To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > Subject: Re: [MS_AccessPros] Report Criteria
> >
> >
> >
> >
> >
> > Thanks John.
> >
> > I should have mentioned that it's one report. How would this work?
> >
> > Toukey
> >
> > --- In MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com> , John Viescas <JohnV@>
> > wrote:
> > >
> > > Toukey-
> > >
> > > If these are three separate reports, use code triggered either by After
> Update
> > > of the selection or a command button to look at the choice and open the
> > > appropriate report. Let's say the choice is in a combo box and triggered
by
> a
> > > command button. The code might look like:
> > >
> > > Private Sub cmdOpenReport_Click()
> > > ' Figure out which report to open
> > > Select Case Me.cmbReportSelect
> > > Case "Insurer"
> > > DoCmd.OpenReport "rptInsurer", acViewPreview
> > > Case "Claim Status"
> > > DoCmd.OpenReport "rptClaimStatus", acViewPreview
> > > Case "Type of Claim"
> > > DoCmd.OpenReport "rptTypeOfClaim", acViewPreview
> > > End Select
> > > End Sub
> > >
> > > You could even be a bit slick about it and put the companion report name
in
> a
> > > hidden column of the combo box. Then all your code needs to do is open the
> > > selected report:
> > >
> > > DoCmd.OpenReport Me.cmbReportSelect.Column(1), acViewPreview
> > >
> > >
> > > John Viescas, author
> > > Microsoft Office Access 2010 Inside Out
> > > Microsoft Office Access 2007 Inside Out
> > > Building Microsoft Access Applications
> > > Microsoft Office Access 2003 Inside Out
> > > SQL Queries for Mere Mortals
> > > http://www.viescas.com/
> > > (Paris, France)
> > >
> > > -----------------------------------
> > >
> > > From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > [mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of toukey1
> > > Sent: Sunday, May 20, 2012 9:39 PM
> > > To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > Subject: [MS_AccessPros] Report Criteria
> > >
> > >
> > > Hi,
> > >
> > > I would appreciate any assistance with a report that is based on input
from
> > the
> > > user. For example, I would like the user to be able to select any or all
of
> > the
> > > three options below:
> > >
> > > Insurer
> > > Claim Status
> > > Type of Claim
> > >
> > > I know I can create a form with the above which the user selects but how
do
> I
> > > get the report to run based on the selection by the user?
> > >
> > > Regards
> > > Toukey
> > >
> >
> >
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>
>
>
>
>
> [Non-text portions of this message have been removed]
>

[Non-text portions of this message have been removed]