Criteria Select in a Report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 'Query A' with a Service Area field (data is W, C, E for West, Central
or East). I would like to remove the 'Like [W,C,E or *(for ALL)] statement
from 'Query A' and move it to the Report. For example, I would like to have
2 control buttons on the menu page. WHen Button 1 is pressed, the report
displays all records from all service area's. WHen Button 2 is pressed, the
report presents a message box "W, C, E". The user selects W, C or E and then
the same report displays with only records for that service area appears. I
do not want to have multiple queries or reports. Thanks for the help.
 
Ernie said:
I have 'Query A' with a Service Area field (data is W, C, E for West, Central
or East). I would like to remove the 'Like [W,C,E or *(for ALL)] statement
from 'Query A' and move it to the Report. For example, I would like to have
2 control buttons on the menu page. WHen Button 1 is pressed, the report
displays all records from all service area's. WHen Button 2 is pressed, the
report presents a message box "W, C, E". The user selects W, C or E and then
the same report displays with only records for that service area appears. I
do not want to have multiple queries or reports.
Click to expand...


It's not clear what you mean by "2 control buttons on the
menu page", but I will assume that you mean buttons on a
form.

The usual approach for this kind of thing is to add a text
box (named txtArea) to the form for users to enter the W,C,E
or nothing (for All). Then a single button can do what you
want using code like this in its Click event:

Dim strWhere As String
If Not IsNull(Me.txtArea) Then
strWhere = "[Service Area] = """ & txtArea & """"
End If
DoCmd.OpenReport "yourreport", acviewPreview, _
WhereCondition:= strWhere
 
the easiest way to do this is in the form, not the report. add code to the
"All" button's Click event procedure, to simply open the report (with no
criteria in the underlying query), as

DoCmd.OpenReport "ReportName"

on the "Specific area" button, you can add code to get the area input from
the user and then open the report with a WHERE clause applied, as

Dim strArea As String
strArea = InputBox("Enter the area.")

DoCmd.OpenReport "ReportName", , , "AreaFieldName = '" _
& strArea & "'"

or preferably, you could use the button to open a form with a combo box on
it, that has the areas listed in the droplist, so the user can't enter an
invalid area; just add a command button to the form to run the OpenReport
action after a value is entered in the combo box.

or, easier yet, just add a combo box to your menu form, so the user can
select the area. in this case you could actually use one button to open the
report, rather than two - with the following code, as

If IsNull(Me!cboArea) Then
DoCmd.OpenReport "ReportName"
Else
DoCmd.OpenReport "ReportName", , , "AreaFieldName = '" _
& Me!cboArea & "'"
End If

hth
 
Thanks, Marshall/Tina. I'll give your suggestions a try. Your assumption
was correct, Marshall. Buttons on a form.

Marshall Barton said:
Ernie said:
I have 'Query A' with a Service Area field (data is W, C, E for West, Central
or East). I would like to remove the 'Like [W,C,E or *(for ALL)] statement
from 'Query A' and move it to the Report. For example, I would like to have
2 control buttons on the menu page. WHen Button 1 is pressed, the report
displays all records from all service area's. WHen Button 2 is pressed, the
report presents a message box "W, C, E". The user selects W, C or E and then
the same report displays with only records for that service area appears. I
do not want to have multiple queries or reports.
Click to expand...


It's not clear what you mean by "2 control buttons on the
menu page", but I will assume that you mean buttons on a
form.

The usual approach for this kind of thing is to add a text
box (named txtArea) to the form for users to enter the W,C,E
or nothing (for All). Then a single button can do what you
want using code like this in its Click event:

Dim strWhere As String
If Not IsNull(Me.txtArea) Then
strWhere = "[Service Area] = """ & txtArea & """"
End If
DoCmd.OpenReport "yourreport", acviewPreview, _
WhereCondition:= strWhere
Click to expand...
 
Back
Top