Inclusive between 4

[vb4me]

Hello,

I have a query that selects data between 2 dates. Say the 1st of May and the 30th of May.

The query works fine except that it does not select records for the 30th. If I want to select dates for the 30th I need to push it 1 day ahead.

Is there a way of making the criteria inclusive?

 

[danvlas]

You could use >=Date1 And <=Date2

But, as far as I know, Between should include both dates...I stand corrected if I'm wrong...

HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[scriverb]

I have seen where the end date of a between does not include the last date before. The problem was that the date field being analyzed was updated with the Now() function which included the time portion of the day. So when you compare between two dates and the last date is May 30 it is comparing to 5/30/2003 0:00 AM. So, a date of 5/30/2003 9:00 am would not be selected. If the field was filled with the Date() function and the time portion was 00:00 am then it would be selected.

Let me know if this seems to be the problem or not.



Bob Scriver

 

[danvlas]

Bob, that definitely deserves a star!

Hate to admit it, but I didn't know that...




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[scriverb]

Thanks Daniel. That one drove me nuts at one time. Something that you don't think about but you must be careful when using the Now() function. There are unknown values there that maybe you don't want to be included.

Bob Scriver

 

[cmmrfrds]

Bob is right about the time protion but there are some easy ways around the problem with the between function.

One is to use the format function to only evaluate the date part of the date field.

Where Format(yourdate,&quot;Short Date&quot between ParmBegin and ParmEnd

You can use any function that will strip off only the integer part (date part) of the date field

where cdate(yourdate) between cdate(ParmBegin) and cdate(ParmEnd) ' or any other function that does the same thing.

OR add the time to the Parm end date.
Dim ParmEnd as string
ParmEnd = &quot;01/01/03 23:59:59&quot;

Where yourdate between ParmBegin and ParmEnd

 

[scriverb]

Almost posted over your comments with the same workaround. Both of these are excellent ways to get this comparison to work without having to perform major surgery on the table.

Deserves a Star!

Bob Scriver

 

[vb4me]

Thanks to all of you who have responded - I have not had time to make changes but it definitely explains things to me and should fix my problem