Generating a report by date range

[Guest]

Hello All

I have a report which is generated using a date range. The Query has been
set up with the following code in the date field:-

Between [Enter Start Date] And [Enter End Date]

This works fine, however if there is any data in the start or end date, this
data does not appear in the report. E.G. If I want to report on the date
range of 1/9/05 until 3/9/05 then the report only generates with any data on
the 2/9/05 only. Any data dated 1/9/05 & 3/9/05 does not appear.

Can someone help here? is there a more reliable way of generating a report
with a date range????

 

[Guest]

Try

=[Enter Start Date] And <=[Enter End Date]

Hello All

I have a report which is generated using a date range. The Query has been
set up with the following code in the date field:-

Between [Enter Start Date] And [Enter End Date]

This works fine, however if there is any data in the start or end date, this
data does not appear in the report. E.G. If I want to report on the date
range of 1/9/05 until 3/9/05 then the report only generates with any data on
the 2/9/05 only. Any data dated 1/9/05 & 3/9/05 does not appear.

Can someone help here? is there a more reliable way of generating a report
with a date range????

 

[Steve Schapel]

Sam,

I would always provide a form with textboxes for the entry of query
criteria, rather than the Parameter Query approach.

But anyway, that is probably not related to your question. Does the
data in your table include a time component as well as the date? If so,
that would explain the date at the end of the range not being included
in the records returned by the query. But not the omission of the
1/9/05 data, I can't think of an explanation for that.

 

[Guest]

Steve

My dates do have the time recorded.

How would one set up a form to filter a report by date???

Regards

Sam,

I would always provide a form with textboxes for the entry of query
criteria, rather than the Parameter Query approach.

But anyway, that is probably not related to your question. Does the
data in your table include a time component as well as the date? If so,
that would explain the date at the end of the range not being included
in the records returned by the query. But not the omission of the
1/9/05 data, I can't think of an explanation for that.

--
Steve Schapel, Microsoft Access MVP

Hello All

I have a report which is generated using a date range. The Query has been
set up with the following code in the date field:-

Between [Enter Start Date] And [Enter End Date]

This works fine, however if there is any data in the start or end date, this
data does not appear in the report. E.G. If I want to report on the date
range of 1/9/05 until 3/9/05 then the report only generates with any data on
the 2/9/05 only. Any data dated 1/9/05 & 3/9/05 does not appear.

Can someone help here? is there a more reliable way of generating a report
with a date range????

 

[Steve Schapel]

Sam,

If you use 3/9/05 as a criterion, Access will regard this to mean
midnight. So if the data has a time component, this is after midnight,
so will not be included. You have two choices to overcome this:
1. Strip the time component out in your query. To do this, you can
make a calculated field in the query that your report is based on, like
this...
BareDate: Int([YourDateTimeField])
.... and then apply your criteria to this column instead of to the
DateTime field itself.
2. Continue to apply the criteria to the DateTime field, and add a day
to the criteria.

Put two unbound textboxes on a form which is open at the time that the
report is printed. Let's call these textboxes DateFrom and DateTo. The
date range for the reports is entered in here. Ok, in the criteria of
the query, put it like this...
Between [Forms]![YourForm]![DateFrom] And [Forms]![YourForm]![DateTo]
.... or, if you use the second approach suggested above...
Between [Forms]![YourForm]![DateFrom] And [Forms]![YourForm]![DateTo]+1

 

[Guest]

Steve

I tried to add the "BareDate: Int([Date Handed Off])" To the query, however
the date came up as numbers, not a date!!!!!

I also tried "Between [Forms]![YourForm]![DateFrom] And
[Forms]![YourForm]![DateTo]+1" However it appears that the query does not
like the "+1"

What am I doing wrong???

Sam,

If you use 3/9/05 as a criterion, Access will regard this to mean
midnight. So if the data has a time component, this is after midnight,
so will not be included. You have two choices to overcome this:
1. Strip the time component out in your query. To do this, you can
make a calculated field in the query that your report is based on, like
this...
BareDate: Int([YourDateTimeField])
.... and then apply your criteria to this column instead of to the
DateTime field itself.
2. Continue to apply the criteria to the DateTime field, and add a day
to the criteria.

Put two unbound textboxes on a form which is open at the time that the
report is printed. Let's call these textboxes DateFrom and DateTo. The
date range for the reports is entered in here. Ok, in the criteria of
the query, put it like this...
Between [Forms]![YourForm]![DateFrom] And [Forms]![YourForm]![DateTo]
.... or, if you use the second approach suggested above...
Between [Forms]![YourForm]![DateFrom] And [Forms]![YourForm]![DateTo]+1

--
Steve Schapel, Microsoft Access MVP

Steve

My dates do have the time recorded.

How would one set up a form to filter a report by date???

Regards

 

[Steve Schapel]

Sam,

I tried to add the "BareDate: Int([Date Handed Off])" To the query, however
the date came up as numbers, not a date!!!!!

That doesn't matter, it will still work.

I also tried "Between [Forms]![YourForm]![DateFrom] And
[Forms]![YourForm]![DateTo]+1" However it appears that the query does not
like the "+1"

What makes you think that? What happened?

What am I doing wrong???

Nothing, as far as I know.