between...and not inclusive?

[pungentSapling]

I am using between... and operator to set the date criteria for a query.... According to the help files this operator is supposed to be inclusive (include both the dates specified) I am finding that it is only icluding the first date and not the second
ie: between#1-1-02# and #1-2-02# only returns records for
1-1-02. If you put the same date in each place it returns nothing....
I know i can fix it by adding a day to the second date but I would rather have the operator include both the specified dates as they are entered



any suggestions?


thanks
p

 

[FoFa]

This usually happens when the column you are checking against is a datetime column. With out specifing a time, the time portion defaults to 00:00:00, hence 2/2/03 00:00:01 is outside the supplied range of BETWEEN 2/1/03 AND 2/2/03.

 

[pungentSapling]

I thought that might have something to do with the problem... So I changed all the formats to medium date on the query and on the underlying table...but I am still having the same problem.

thanks for the reply.

 

[FoFa]

I have found changing the format does not always work depending on what version of access and what you are trying to do (I don't know for sure about this case). Have you tried >= and <= instead of between to see if the results change?

 

[pungentSapling]

I tried the >= and <= with the same results....I guess I will have to use dateAdd to add a day to my outer date.

thanks for the suggestions

p

 

[FoFa]

Or you can cancatonate 23:59:59 as a time to the second date, you should try that anyway to see if that is causing your problem, but truthfully, I usually add 1 to the date.

 

[Pat Hartman]

Changing the format of a column only affects how data is displayed. It does not have any impact on how data is stored. To overcome the problem, change the format of the date in the query.

Where Format(YourDate,"Short date") Between [FromDate] and [ThruDate]

In the above example, you are "extracting" the date part of the date/time field.