Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Reports / Printing / January 2006

Tip: Looking for answers? Try searching our database.

Criteria Select in a Report

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Ernie Sersen - 07 Jan 2006 16:46 GMT
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.  
Marshall Barton - 07 Jan 2006 17:57 GMT
>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
[quoted text clipped - 4 lines]
>the same report displays with only records for that service area appears.   I
>do not want to have multiple queries or reports.

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

Signature

Marsh
MVP [MS Access]

Ernie Sersen - 08 Jan 2006 19:51 GMT
Thanks, Marshall/Tina.  I'll give your suggestions a try.   Your assumption
was correct, Marshall.  Buttons on a form.  

> >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
[quoted text clipped - 20 lines]
> DoCmd.OpenReport "yourreport", acviewPreview, _
>         WhereCondition:= strWhere
tina - 07 Jan 2006 18:00 GMT
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

> 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
[quoted text clipped - 4 lines]
> 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.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.