Solved

Have criteria in report header?

Posted on 2000-03-06
7
212 Views
Last Modified: 2009-12-16
When we run a report it asks for two dates (start and finish), how can we have these show in the report header?
0
Comment
Question by:treyjeff
7 Comments
 
LVL 6

Accepted Solution

by:
blakeh1 earned 5 total points
ID: 2588943
Your best bet would be to declare the parameters in the query. Under the Query Menu choose, paramters, enter in the values. After this add a field for each parameter to the grid as new fields. then you can just add them directly from the field list in design view for the report.
0
 

Expert Comment

by:rpm97
ID: 2589198
Another way would be to create a form with two unbound text boxes, one for beginning date (beginningdate_txt) and one for endingdate (endingdate_txt).  Then in the criteria section of the your query for the date on which you are running your report, enter "between forms!yourform!beginningdate_txt and forms!yourform!endingdate_txt" (without the quotes).

When running the report, you can set yourform.visible = false, and add the fields into your report the same way as in the criteria in the query (forms!yourform!beginningdate_txt and forms!yourform!endingdate_txt)

0
 
LVL 7

Expert Comment

by:Believer
ID: 2589340
A third option...
I have grown *very* fond of putting report parameters into a local work table.  Then I tie the table into the query used by the report.  Voila!  No more Forms!Formname!FieldName...
Can also run reports *without* having the form open.
Here's a work table, tblOptions, for your specific situation (e.g. from/to date parameters):
bytWork is a byte field with:
  default value 0 (zero),
  validation rule"=0",
  Required=Yes,
  Indexed=Yes (No Duplicates)
datFrom is a date field
datTo is a date field

bytWork is only in the table to make sure that only one record ever gets put into the table.

Go into the query that your report is based on and add tblOptions to it.  Do not link this table to any other table in the query.  Add the fields [datFrom] and [datTo] to the query grid.  On the criteria row for the date field used by your report, type:
  Between [datFrom] And [datTo]

In your report, [datFrom] and [datTo] will now be available as normal fields that you can drop in the report header.

Side note: If your query happens to already have multiple tables and any of them are joined with arrows (Left outer join, etc.) then you will get an error when you run this.  To solve that problem, don't put tblOptions in the query.  Instead, define a NEW query which contains tblOptions and the original query.  Drag all the fields to the grid from your original query and tblOptions.  Add the "Between" criteria as described above.  To get these dates to show up in your report header, put them all by themselves in a subReport based solely on tblOptions.

Now for the user interface which runs the report... Bind the form to tblOptions and drop the fields on the form.  Add a buton to open the report.  In the code behind the button, *before* you run the report, DoCmd.RunCommand acCmdSaveRecord.  That will force access to save the dates to tblOptions before the report runs.

<Whew.>  I think I covered everything.  If not, I'm sure I'll hear about it! (lol)  This may sound like a bit of work, but it really ain't that bad.  It makes overall application design a lot easier!  You won's miss those "Forms!Formname!FieldName..." expressions a bit, I promise!!

0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 1

Author Comment

by:treyjeff
ID: 2589452
Well in our query we have it set under query to ask for start date and finish date :)  I just want the easiest way :)
0
 
LVL 7

Expert Comment

by:Believer
ID: 2589471
treyjeff,
I'm all for easy, too, but I'm also for nice-to-the-user.  (I'm not trying to be mean to you, I promise!)
Another cool side-effect is that the criteria the user enters are saved.  Next time the report prompt opens, they can see the values they used last time.
0
 
LVL 7

Expert Comment

by:Believer
ID: 2589496
A couple more general comments about using this method...

First off, this method is *not* well-suited to a front-end that is run by multiple users.  By that I mean if several people have shortcuts to the (front-end) database running on a network.  Each user's change to the report parameters can affect the other users.  If you install a separate font-end on each user's machine then you will have no problem.

Next, if you are doing a different kind of parameter this gets even easier.  Let's use "Customer" as an example...  If you have a table of customers (tblCustomers), add the customer field, lngCustomerID, to tblOptions and make it a combo box on the report prompt form (assuming that you have a customer lookup table, of course).  Now when you add tblOptions to the report's query, link [tblOptions].[lngCustomerID] to [tblCustomers].[cntCustomerID].  The query is now customer-specific... no query criteria!  Only records where [tblOptions].[lngCustomerID] match [tblCustomers].[cntCustomerID] will be included in the recordset.

That's all I can think of for now...
0
 
LVL 1

Author Comment

by:treyjeff
ID: 2597744
It worked :)
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now