parameter query

[David]

I have a "simple" database without joins. I use a
parameter query with the "between" and "and" criteria to
limit records prior to a monthly report. I just
discovered that even though this command is supposed to
produce all dates inclusive of beginning and ending it
EXCLUDES the ending date if both values are in the same
month (ex. 6/1/04 6/30/04).The records for 6/30 will be
omitted. I checked this out using other ending days but
the same month and experienced the same result. I now
have 3 years of bad monthly summaries. Can anyone help me
please

 

[Dale Fye]

David,

My guess is that the date field that you are using in the
query actually contains dates and times. When you do a
comparison between 6/1/04 and 6/30/04, what Access really
does is a comparison that looks like

BETWEEN 6/1/04 00:00:00 and 6/30/04 00:00:00

You should be able to modify your query as follows:

WHERE DateValue([yourDateField]) BETWEEN 6/1/04 AND 6/30/04

The DateValue() function strips the time portion of the
field out of the equation. This is also the way to modify
your date field if you want to do an aggregation by day.

 

[fredg]

I have a "simple" database without joins. I use a
parameter query with the "between" and "and" criteria to
limit records prior to a monthly report. I just
discovered that even though this command is supposed to
produce all dates inclusive of beginning and ending it
EXCLUDES the ending date if both values are in the same
month (ex. 6/1/04 6/30/04).The records for 6/30 will be
omitted. I checked this out using other ending days but
the same month and experienced the same result. I now
have 3 years of bad monthly summaries. Can anyone help me
please

In all probability you have stored a time value with the dates. This
will occur if the date was entered using the Now() function instead of
Date().

A date entered on 6/30/2004 at 3:00 PM is not going to be found if you
search for record up to 6/30/2004 (i.e. midnight, which is the default
time value).

You can do one of the following:

1) Add 1 day manually to the entered parameter value, i.e. enter
7/1/2004 instead of 6/30/2004,

or..
2) Have the parameter add it for you automatically.

Open the query in Design View.
Click on Query + Parameters
Type

[Enter Start Date] Date/Time
[Enter End Date] Date/Time

in the parameters dialog box (make sure the spelling is exactly the
same as the parameter prompts).

Then as the actual parameter, write:
Between [Enter Start Date] AND ([Enter End Date]+1)

or...

3) You can run an update query and permanently remove the time value
from the date field:

Update MyTable Set MyTable.DateField = Int([DateField]);

Then make sure new entries do not include the time value.

 

[Tom Ellison]

Dear David:

I don't think your problem is that it excludes the ending date. I
believe it is probably the case that your "date" values include not
just dates, but times of day.

Now, if you tell a query to limit the results:

BETWEEN #06/01/04# AND #06/30/04#

it will find only those rows between 06/01/04 00:00:00 and 06/30/04
00:00:00. That will include everything done on 06/01/04 but will
probably exclude everything done on 06/30/04. If you change that to
include the whole day on 06/03/04, it would work much better:

BETWEEN #06/01/04# AND #06/30/04 23:59:59.99#

Now, if you never have anything posted at exactly midnight, then you
could simply go:

BETWEEN #06/01/04# AND #07/01/04#

but if something ever does happen exactly at midnight, then it would
show up in two separate months.

A better way might be:

= #06/01/04#

< #07/01/04#

If it is truly the case that this happens only when both dates are in
the same month, and not as I've described above, then I'm really
stumped. The above theory is all I have to offer right now.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts