' BETWEEN ' 2 dates not inclusive is time is in format

I use a query based on 2 functions; [FromDate] and [ToDate]. Based on the help file for 'between and....' which states that useing the between function is inclusive. And it is, sort of, but not really.
What I've found is that BETWEEN only is inclusive if there is no time as part of the format. Cuz 1/1/02 is not the same as 1/1/02 12:35 PM

Which stinks cuz this means that all my reports have been slightly off for the past few months.

**If this is not the case, and I'm just doing something else wrong, please let me know.

Two suggestions/comments:

1. Do you need the time??? If not, take it off and then your dates will be okay.

2. If you need to keep the time, instead of the parameter query by itself, create a function that automatically tacks on a 00:01 AM to the end of the first date you enter and a 12:59 PM on the end of the second date. This will then make the date/time field all inclusive. The secret to creativity is knowing how to hide your sources. - Albert Einstein

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

Here is a quick bit of code that I made work.....

'*********** Start Code ****************
Public Function FixDate(dteDate As Date, Optional strEnding As String = "12:01 AM&quot As Date

Dim strMyDate As Date
strMyDate = CStr(dteDate) & " " & strEnding

FixDate = CDate(strMyDate)

End Function
' ********** End Code ***************** The secret to creativity is knowing how to hide your sources. - Albert Einstein

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

That's just it, I wasn't aware that 'Between' would not be inclusive if the time was part of the format. So I didn't even worry about the time being on it.
After all, why create more work for yourself if you don't have too. But alas, nothing is that easy.
I just reformated the field which then had to be converted back to a date format.

Thanks for the code,
Dave

Dave,

Actually Between is inclusive even with the time....the problem is that Access looks for EXACT data syntax....so if you have a date field with 1/1/02 12:23 PM and you only tell the Be statement 1/1/02...everything with 1/1/02 and a time will not match.

So in reality, Access was doing EXACTLY what you told it to do...

This falls into the "bug" area of a logic error, probably one of the worst to track down and correct in my opinion.

Glad you got it corrected. The secret to creativity is knowing how to hide your sources. - Albert Einstein

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

This is one of those 'Obi-Wan' things right?
"From a certain point of view"