Not picking up last date on reports

  • Thread starter Thread starter Skygirl
  • Start date Start date
S

Skygirl

When I run a report asking for start date (3/1/05) and ending dat
(3/31/05), the records for the last date don't show on the report.
have to run the report for 3/1/05 to 4/1/05 to get the records fo
3/31

I've been able to fix it by repairing the database, creating a ne
field, updating the new field with old field and deleting the ol
field. But now it's happening in other tables and this fix isn'
working for them

Can anybody help me

Thank
 
Are you storing the time in the field? Do you understand that 3/31/2005
5:00:00 would not be included in your range?
 
In the field where I do the repair, no it's just stored as a short date, but
I have other fields that are date and time and I'm having problems with those
reports too.

Have you ever heard of this problem?

Thanks for your help,
Lily
 
There is no such thing as storing a "short date". I expect you mean just the
date value. Don't get confused with displayed formats (short date) and
actual data types.

If a range is not returning what you perceive as the correct records, I
expect you have a time associated with your dates.
What do you get if you try
Between #3/1/05# AND #3/31/05 23:59:59#
 
In the field where I do the repair, no it's just stored as a short date, but
I have other fields that are date and time and I'm having problems with those
reports too.

Have you ever heard of this problem?

Thanks for your help,
Lily
Click to expand...

Regarding:> Have you ever heard of this problem?<
You have a miss-conception of how Access processes dates.
If your records have been entered using Now(), they have a date and
time value.

A record with a date entry of 3/31/2005 is not the same as a record
with a date entry of 3/31/2005 00:01:00 (i.e. 1 minute after
midnight).
The first will be returned using between [Start date] and [End date],
the second will not, as it is later than the End Date of 3/31/2005
midnight

Add 1 day to the expected end date.
or ... write your query so it adds the extra day for you:

Between [Enter Start Date] And DateAdd("d",1,[Enter End Date])

You MUST enter the exact parameters and their datatype in the Query
Parameter dialog box.
In Query design view, click on Query + Parameters
[Enter Start Date] Date/Time
[Enter End Date] Date/Time
 
I can't add the date parameters in the query because it's a repor
that the users enter the dates when they run it. Does the user hav
to enter the date and time when running the report? Will that solv
the problem

Sorry I'm new to these forums and I'm not sure if I'm doing thi
right

Thanks
Lil
 
Skygirl,
Use Parameters in your query. Usually Parameters are used to get a
StartDate and EndDate range for the report to use as a filter.
In your query, in your Date field, use this criteria...
Between [Enter Start Date] And [Enter End Date]
The 2 bracketed items are parameters. When the query does run, it has no
idea what those parameters are, so... it asks the user.

When the report runs, and consequently the query, the user will first be
asked for...
Enter Start Date
in an input box where they can enter a starting date... say 1/1/05.
Then the user will be asked for...
Enter End Date
and another imput box that the user can use to enter the ending date... say
3/1/05

Those entered values will be used by the query to return records with a
date between the two entered "Parameters".

hth
Al Camp
 
Back
Top