In this article, we attempt to understand the possible reasons for the date criteria not working, and how to make it work.
There are moments when something turns out to be much more complicated than what we would assume it to be. It might have happened with you that you are trying to make use of one of the given functions of MS Access, but are either unable to use it in the best possible way, or are completely unable to use it. One such confusing feature is the Date Criteria in MS Access. There are chances that when you are attempting to make use of this feature, it does not end up giving you the desired result. One of the main reasons why this happens is because you are entering invalid criteria.
How to deal with Date Criteria not working in MS Access
It might also happen that the date criteria do provide you with results, but they might not be accurate. In this kind of a situation, things become even more challenging for you. But there is nothing to worry about because this problem can easily be dealt with the use of “And” operator, which you need to insert between the dates that you want to use in the query. Once you have inserted this “And” operator in Date criteria, your queries will begin giving you the kind of results that you want them to deliver. Apart from the use of this “And” operator, there are other techniques as well, that you can try out to get rid of this problem.
Make use of “Between”
One of the most reliable solutions apart from the “And” operator is “Between” which can also be used when using Date criteria. As a user, you are advised to make use of this “Between” operator instead of using the equal to, greater than or less than signs. These end up making operations complex, unlike “Between” which makes things easier. Using “And” and “Between” together works best if you want the date criteria to give you exact results. Given below is an example to help you understand how you can get it done.
Between #1/1/2017# and #6/6/2017#
It might be confusing for you to understand why these pound signs are enclosed along with the dates. These are there to indicate that the date has been recognized by MS Access. When Access is able to recognize the mentioned dates, the sign appears automatically.
Recognizable formats for Date Criteria
Simply understanding the right set of characters to put to get the desired results is not enough, you also need to understand the key recognizable formats for Date Criteria in MS Access. In contrast to the common mm/dd/yyyy, Access recognizes the m/d/yyyy format, m/d format, (this should be used if you want to refer to the current year only). You can also use the d/mmm/yyyy format or the d/m/yyyy format.
Using the date criteria in MS Access is not complex or challenging, but can be a bit tricky, but not if you understand well how to make the best use of all the acceptable characters.
While working on the Access application, users must be alert to incidents of Access damage. To avoid incidents of data loss invest in a state of the art recovery tool like DataNumen Access Repair.
Author Introduction:
Vivian Stevens is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including sql recovery and excel recovery software products. For more information visit www.datanumen.com
I use >= mm/dd/yyyy and I have been for years. Now all of a sudden it isn’t including all the data for the last 5 entries I made when I used this criteria. Those entries are within the dates I am putting in the criteria. Am I doing something wrong all of a sudden???