Using Between with General Date format

[Guest]

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?

 

[Guest]

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

 

[Guest]

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

Debug.Print Date() + .99999 = 8/22/2006 11:59:59 PM
--
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.

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?

 

[Guest]

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