Date Criteria May Not Always Work With Ms Access Query

By Author: Ben Beitler
Total Articles: 4
Comment this article

Learning about ms access query is the means to a good database management system as it is the heart of any database application.

There are many ways and questions to ask a database using queries and mastering the
special conventions and criteria will pay the dividend and avoid silly mistakes, the illogical recordset results, and even errors.

One of the more commonly used criteria and prone to errors (if misunderstood) is the date/time data type and its conventions.

Take a look at the ms access query below-showing orders before the year 2016 and the intend
of the query which suggested a date range from 1st January 2016 to 31st December 2016.

The criterion for the above shows >=#01/01/2016 < #31/12/2016# which will not actually return the correct range and instead show what’s known as logical errors (dates outside the range).

It’s missing the ‘And’ operator and better still adding the ‘Between’ hand will capture the correct range too. The # (hash sign) is the exact convention and good check to ensure it’s the right data type ...
... too (date/time in this case).

A revised example is shown below:

If the field is a actual date/time data type, you do not need to enter the # (hash sign) as it will automatically fill this in for you when moving the cursor elsewhere in the restrain (another convention checker for you).

If however, you see double-quotes (“ “) wrapped around the date expression, this suggests either you have chosen the wrong field or the date field is actually a text data type and it will simply not work as expected.

One final point to consider and even check for you ms access query database and those
queries are the regional setting of the operating system you are using. UK versus USA setting often throws up confusion and inaccuracies to so ensure you are using the correct date/time formats.

MS Access query is very powerful indeed and there are many permutations available – take
a look at my eBook Microsoft Access 2016 Database Queries.

Do you need for eBook relating to Access Database?

Here, you can check : https://accessdatabasetutorial.com/ebooks/

My name is Ben Beitler (based in London, UK) and I have been building, consulting & training with Microsoft Access databases for over 20 years.