Limiting data based on entry date

[HeatherD25]

Hi,

I have a form where a user can select a begin date and end date from a drop
down menu. I have a report that I would like to open after they select the
date. The report is based on 3 different queries -- basically the 1st query
has to run, then the 2nd query has to run, and then the 3rd query has to run
in order to get to the final data that I want to display on my report. The
date limitation is on the first query where I would like to limit the data
set. How do I program this in Access to limit the data on the first query,
and then run query 2 & 3, and then open the report?

Thanks!!
Heather

 

[Stefan Hoffmann]

hi Heather,

How do I program this in Access to limit the data on the first query,
and then run query 2 & 3, and then open the report?

If the date field is on a bound form, then use

Forms![yourDateSelectionForm].Form![yourDateField]

as criteria in your query. Otherwise use

Forms![yourDateSelectionForm].Form.[yourDateCombo].Value

as criteria.

The form must stay open, while running your query.


mfG
--> stefan <--

 

[HeatherD25]

Do I put that as the "Between... And" statement in the query criteria line?
Would I create a macro to run each of the queries and then open the report?

hi Heather,

How do I program this in Access to limit the data on the first query,
and then run query 2 & 3, and then open the report?

If the date field is on a bound form, then use

Forms![yourDateSelectionForm].Form![yourDateField]

as criteria in your query. Otherwise use

Forms![yourDateSelectionForm].Form.[yourDateCombo].Value

as criteria.

The form must stay open, while running your query.


mfG
--> stefan <--

 

[Dale Fye]

Heather,

If queries 2 and 3 are based on query #1, then you may need to do the
following.

1. You will definately need to keep your form open throughout this entire
process.
2. As Stefan indicated, you first need to write query #1 so that it refers
to the dates on your form. That query might look like:

SELECT * FROM yourTable
WHERE [DateField] BETWEEN [Forms]![yourFormName].[txt_StartDate]
AND [Forms]![yourFormName].[txt_EndDate]

You might also have to add parameter declarations to each of the queries in
order for queries #2 and #3 to work properly.

To do this, open each of the queries in design view. Right click in the
upper portion of the query grid (but not on a table), and select the
Parameters option. In the left column enter the name of your parameters, and
in the right column, select Date/Time.

Using my example above, if you view your query in SQL view, it should look
something like:

PARAMETERS [Forms]!yourForm.[txt_StartDate] DateTime,
[Forms]!yourForm.[txt_StartDate] DateTime ;
SELECT * FROM yourTable
WHERE [DateField] BETWEEN FORMS![yourFormName].txt_StartDate
AND Forms![yourFormName].txt_EndDate

You may have to add these parameter lines to queries #2 and #3 as well, but
I would try them without the parameters first. Now, create your reports
based on queries #2 and #3. If you don't have your form open when you run
these queries, you will prompted for the values.

Once you have your reports designed and running the way you want, you should
add a couple of buttons to your form to open each of the forms. You can use
the wizard to let Access write the code for this.

HTH
Dale