Date Ranges

[Ripley]

Wonder if anyone can help with a problem I have trying to get my database to
display a date range correctly?

Basically, the database is used for Transport purposes and enables the
userto report on such things as delivery failures, reason for failing, reason
code totals, etc. I have a few tables, two of which are the main tables used
for inputting data. One such table is called "Returns Sheet" and contains a
"Delivery Date" field. There's a form called "Reason Counts" which asks the
user to input a date from and a date to. When they then click on the Print
Preview button this causes a query called "Reason Counts" to run. On this
query is the Delivery Date field from my Returns Sheet table. I have it
listed twice; once with a criteria which says >= the date from value on the
Reason Counts form, and <= the date to value on the Reason Counts form ...
and the other is listed there with no criteria.

This query then displays a report called "Reason Counts" which has some
sorting and grouping options configured. However, at the top of the report I
would like the date range listed AS TYPED IN BY THE USER on the Reason Counts
form earlier.

If I show the fields from the query, I get the same date displayed in both
of the Delivery Date fields (in other words if I choose a date range of
10/5/06 to 17/5/06 I just get 10/5/06 and 10/5/06 displayed). I have tried
adding a new field to my report which runs from the form itself, but then
when you click on the print preview button another pop up box asks you to
enter the date yet again.

Sorry for such a longwinded question, but is it possible to do what I am
trying to do, and have I just gone about things the wrong way?

I would appreciate any help with this. Thanks.

 

[Allen Browne]

Since the form is open when you run this report, you can refer to the text
boxes on the form.

The Control Source of the text box on your report would be something like
this:
=Format([Forms].[Reason Counts].[date from], "Short Date") & " to " &
Format([Forms].[Reason Counts].[date to], "Short Date")

 

[Ripley]

Excellent, that works a treat. Thank you very much!

Since the form is open when you run this report, you can refer to the text
boxes on the form.

The Control Source of the text box on your report would be something like
this:
=Format([Forms].[Reason Counts].[date from], "Short Date") & " to " &
Format([Forms].[Reason Counts].[date to], "Short Date")

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

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

Wonder if anyone can help with a problem I have trying to get my database
to
display a date range correctly?

Basically, the database is used for Transport purposes and enables the
userto report on such things as delivery failures, reason for failing,
reason
code totals, etc. I have a few tables, two of which are the main tables
used
for inputting data. One such table is called "Returns Sheet" and contains
a
"Delivery Date" field. There's a form called "Reason Counts" which asks
the
user to input a date from and a date to. When they then click on the Print
Preview button this causes a query called "Reason Counts" to run. On this
query is the Delivery Date field from my Returns Sheet table. I have it
listed twice; once with a criteria which says >= the date from value on
the
Reason Counts form, and <= the date to value on the Reason Counts form ...
and the other is listed there with no criteria.

This query then displays a report called "Reason Counts" which has some
sorting and grouping options configured. However, at the top of the report
I
would like the date range listed AS TYPED IN BY THE USER on the Reason
Counts
form earlier.

If I show the fields from the query, I get the same date displayed in both
of the Delivery Date fields (in other words if I choose a date range of
10/5/06 to 17/5/06 I just get 10/5/06 and 10/5/06 displayed). I have tried
adding a new field to my report which runs from the form itself, but then
when you click on the print preview button another pop up box asks you to
enter the date yet again.

Sorry for such a longwinded question, but is it possible to do what I am
trying to do, and have I just gone about things the wrong way?

I would appreciate any help with this. Thanks.