Running a Series of Queries from a Form

  • Thread starter Thread starter Gary O
  • Start date Start date
G

Gary O

Here is what I would like to do, and have little knowledge as to how
to do it. I would like to create a form that has 2 fields - start
date and end date. I need the start date and end date stored in
memory so that you can select 5 different queries that when run will
filter a database for this date range. I would also like to take one
of the fields in the database that is being queried and divide it by
the number of days in the date range. If anyone has any ideas, or
even better, has an example I would love to see it.

Thanks in advance for any help you can give me.


Gary
 
Assuming a form named "frmGetDates", with text boxes named "txtStart" and
"txtEnd", you can filter to that date range in your query.

In query design view, in the Criteria row under the date field, enter:
Between [Forms].[frmGetDates].[txtStart] And [Forms].[frmGetDates].[txtEnd]

You can do that in 5 queries if you wish. As long as the form stays open in
the background, the queries will read the dates.

You can also type a calculated field into your query. Type something like
this into the Field row in query design:
[SomeField] / DateDiff("d", [Forms].[frmGetDates].[txtStart],
[Forms].[frmGetDates].[txtEnd])
 
The dates are both inclusive, so it is quite possible for the query to
return records even if the Start and End dates are the same.

If your field has a time component - e.g. its Default Value is set to
Now() - use:
= [Forms].[frmGetDates].[txtStart] And < DateAdd("d", 1,
Click to expand...
[Forms].[frmGetDates].[txtEnd])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen Browne said:
Assuming a form named "frmGetDates", with text boxes named "txtStart" and
"txtEnd", you can filter to that date range in your query.

In query design view, in the Criteria row under the date field, enter:
Between [Forms].[frmGetDates].[txtStart] And [Forms].[frmGetDates].[txtEnd]

You can do that in 5 queries if you wish. As long as the form stays open in
the background, the queries will read the dates.

You can also type a calculated field into your query. Type something like
this into the Field row in query design:
[SomeField] / DateDiff("d", [Forms].[frmGetDates].[txtStart],
[Forms].[frmGetDates].[txtEnd])

Gary O said:
Here is what I would like to do, and have little knowledge as to how
to do it. I would like to create a form that has 2 fields - start
date and end date. I need the start date and end date stored in
memory so that you can select 5 different queries that when run will
filter a database for this date range. I would also like to take one
of the fields in the database that is being queried and divide it by
the number of days in the date range. If anyone has any ideas, or
even better, has an example I would love to see it.

Thanks in advance for any help you can give me.
Click to expand...
Click to expand...
 
Back
Top