SQL BETWEEN If end date is less than start date all records betweenare still displayed.

[Avid Fan]

I noticed that this query shows records the records between even if the
second date is larger than the first.

No big deal really I just was not expecting it.

I suppose you have to code around it in case somebody makes a mistake.

 

[Marshall Barton]

I noticed that this query shows records the records between even if the
second date is larger than the first.

No big deal really I just was not expecting it.

I suppose you have to code around it in case somebody makes a mistake.

Between doesn't care which one is earlier. If it matters to
you, don't use Between. Instead use something like:

datefield >= startdate And datefield <= enddate

 

[John Spencer]

Oh and only in Access SQL.

In other versions of SQL you may very well find that the smallest value MUST
be first. If not, no records will be returned.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County