Does BETWEEN include both dates? (1 Viewer)

[Gkirkup]

I am using Between Me.Firstdate and Me.Lastdate in a query. I thought that it would include both Firstdate and Lastdate in the range. But it appears that it does not. Is there a way to include Firstdate and Lastdate in the range of dates?

Robert

 

[David R]

Is it a date field or a datetime field? You may need to add 1 to Enddate.

 

[jdraw]

YesBetween in Access includes the endpoints.

http://office.microsoft.com/en-ca/access-help/between-and-operator-HP001032212.aspx

Note: This isn't true in all software.

 

[Galaxiom]

I use this expression to include the whole of the LastDate where time is included in the data:

DateAdd("s", -1, Me.LastDate + 1)

 

[Gkirkup]

Yes, this is a DateTime field. I have not seen this before using BETWEEN with date fields. Do datetime fields work differently in BETWEEN?

Robert

 

[Galaxiom]

Do datetime fields work differently in BETWEEN?

No difference but if the data has a time component then the times after midnight must be considered because a date without a time component is midnight at the start of that day.

You data may be displaying date only but can still have a time component included. Check out the format property of the field in the table. It could be limiting what you see.

 

[Pat Hartman]

When all you care about is the date portion of a datetime field, use DateValue to extract the date.

Where DateValue(YourDate) Between StartDate AND EndDate;

Guess what function you would use to extract just the time

 

[Galaxiom]

When all you care about is the date portion of a datetime field, use DateValue to extract the date.

Where DateValue(YourDate) Between StartDate AND EndDate;

Unfortunately, as simple as it seems, this is not really a good alternative to Between.

The DateValue technique requires that the function be applied to every value in the table before the Where condition can be applied.

The Between technique can read the value from the field directly. Moreover it allows the database engine to use the index on the field to return the values in the range.

The Between technique can easily be one hundred times faster. This difference might not be noticeable in small amounts of test data but a lengthy delays become the norm as the number of records grows.

It is essential that the developer understand the processing cost of different techniques. One of the first rules is to avoid queries that involve processing every record before selecting.

This principle can make a an enormous difference in subquery structures. Applying a condition to limit the dataset before applying functions to each record can make an otherwise impractically slow query perform quite acceptably.

 

[gemma-the-husky]

between does include the endpoint, but the endpoint will be treated as 12.00am (ie midnight) at the START of the day.

so if your end points include a time element, they will not get selected.

note that going to next day may result in picking some items from the next day - so you need to understand how it all works, and be careful to manage the query the right way.

 

[Pat Hartman]

Good point. Your expression works on the variable and so is executed only once which would make it more efficient in a query. I tend not to store time in dates unless I'm trying to log an action and you rarely do date range queries on those fields.

Gkirkup, To prevent the problem from the beginning, use Date() when you want only the date and use Now() when you want both.

 

[Brianwarnock]

I use this expression to include the whole of the LastDate where time is included in the data:

DateAdd("s", -1, Me.LastDate + 1)

It is a long time ago but I think I used to use

Me.lastdate + #23:59:59#

I wonder how these to expressions compare, also perhaps if efficiency is the key maybe we should think of

>= me.firstdate and < me.lastdate +1

Rather than using the Between construct, I am asking not confirming.

Brian

 

[RainLover]

I usually use >= Start and <= End Plus One

I have never had a problem. But I am sure I am about to be told otherwise.

Please feel free to enlighten me.

 

[Isskint]

A very useful topic to those using Date AND Time in a field. I am with RainLover, i tend to use >= Start and <= End + 1. I can not remember why i started using that instead of BETWEEN, probably frustration and not get the right data!!

Guess what function you would use to extract just the time

ohh, ohh, ohh would that be TimeValue(YourDate)??????????

 

[Brianwarnock]

As far as I am aware between ..and.. Is the same as >= and<= however if you use these and lastdate +1 you have the potential for an error

Say you specify 23 jan 2013 then you would also pickup any record with 24/01/13 00:00:00 , if you just use < then you will not.

Brian