Using Between with General Date format

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

LauriS - 22 Aug 2006 21:03 GMT

I have a field that is set as a General date and contains date and time.
Example: 7/31/2006 11:18:00 PM.

I'm using Between with parameters:

Between [Enter begin date] and [Enter end date]

Problem is if they enter 7/31/06 as the end date the example above is NOT
included in the result set. I always thought the Between was inclusive - it
included the dates actually used.

If I enter 8/1 as the end date it will pull the example above.

I even tried changing the criteria to >= and <= but it still didn't pull the
example above when I use 7/31/06 as the end date.

I know I can get around this by adjusting the dates by 1 but I don't think
it's right to HAVE to do that.

Am I doing something wrong? Is this a known issue?

Reply to this Message

Jerry Whittle - 22 Aug 2006 21:20 GMT

All is right in the Access world on this one. 7/31/2006 is at midnight as
there are no hours/minutes/seconds.

7/31/2006 11:18:00 PM is 23 hours and 18 minutes later so it can not be
between an earlier date and 7/31/2006.

One cheat is to use something like this:
Between [Enter begin date] and [Enter end date] + .99999

Debug.Print Date() + .99999 = 8/22/2006 11:59:59 PM

Signature

Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

> I have a field that is set as a General date and contains date and time.
> Example: 7/31/2006 11:18:00 PM.
[quoted text clipped - 16 lines]
>
> Am I doing something wrong? Is this a known issue?

Reply to this Message

LauriS - 22 Aug 2006 21:31 GMT

The expression is typed incorrectly or is too complex to evaluate.I tried
that and got the following error message:

The expression is typed incorrectly or is too complex to be evaluated.

I even tried changing it to:

Between [Begin] and ([End] + .9999)

Same error message.

Lauri

> One cheat is to use something like this:
> Between [Enter begin date] and [Enter end date] + .99999
[quoted text clipped - 21 lines]
> >
> > Am I doing something wrong? Is this a known issue?

Reply to this Message

LauriS - 22 Aug 2006 21:40 GMT

I found a way around my problem. For this report the user will be running it
monthly. I created this calculated field in my query:

Month: DatePart("m",[tablename].[DateTime_field])

Then in the criteria for that field I have a parameter set up to prompt them
for the month (the number of the month).

It works.

Lauri

> I have a field that is set as a General date and contains date and time.
> Example: 7/31/2006 11:18:00 PM.
[quoted text clipped - 16 lines]
>
> Am I doing something wrong? Is this a known issue?

Reply to this Message