Access BETWEEN timestamp problem

This Access tutorial explains the problem with BETWEEN timestamp, where the end date is not included in the result.

Access BETWEEN timestamp problem – end date not included

Very often you may receive data source that uses timeStamp (for example, 1/1/2015 15:34:00) instead of a Date (1/1/2015). The purpose of timestamp is to accurately record the time of action for auditing.

In Access (even other system), you may want to select data within specific date period, say, between 1/1/2014 and 1/1/2015. If the Field you want to apply criteria on is Date format, you can see results are able to correctly return both 1/1/2014 and 1/1/2015.

However, if you try to apply criteria between 1/1/2014 and 1/1/2015 to Field with timestamp, you will not be able to return any data on 1/1/2015.

To summarize the above:

When you use date criteria on two dates, both Begin Date and End Date are inclusive in the result.

When you use date criteria on two timestamp, End Date is not inclusive.

Reason – Access end date not included in BETWEEN timestamp

Use the same example as above.

When you apply criteria, between 1/1/2014 and 1/1/2015 on timestamp Field, Access needs to convert date to timestamp in order to do comparison. Because Access has no information what specific hh:mm:ss the date is, Access puts 00:00:00 in the date criteria.

Therefore the criteria between 1/1/2014 and 1/1/2015 is converted to between 1/1/2014 00:00:00  and 1/1/2015 00:00:00

After adding 00:00:00 to date, here comes the problem:

– If your database has a date which is 1/1/2014 01:00:00, it will return in the result because it is really between 1/1/2014 00:00:00  and 1/1/2015 00:00:00.

– If your database has a date which is 1/1/2015 01:00:00, it fails to return in the result because it is not between 1/1/2014 00:00:00  and 1/1/2015 00:00:00.

Solution- Access end date not included in BETWEEN timestamp

We have no problem using date in date Field because everything are considered 00:00:00. Similarly, to solve the timestamp issue, we can take away the hh:mm:ss part from timestamp.

To remove the time value, write an Expression as below

DateValue(TIMESTAMP)

See the below explanation for Access DateValue Function

Syntax of DateValue

DateValue ( date )

The required date argument is normally a string expression representing a date from January 1, 100 through December 31, 9999. However, date can also be any expression that can represent a date, a time, or both a date and time, in that range.

Outbound References

https://support.office.com/en-us/article/DateValue-Function-03878F08-B0DB-42DF-8A0C-279939637C6F

 

 

Access BETWEEN timestamp problem