Learning about ms access query  is the key 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 dividend and avoid silly mistakes, illogical record set results and even errors.

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

Take a look at the ms access query below showing orders before the year 2016 and the design of the

query which suggested a date range from 1^st January 2016 to 31^st December 2016.

The criteria for the above shows >=#01/01/2016

It’s missing the ‘And’ operator and better still adding the ‘Between’ operator will capture the correct range too. The # (hash sign) is the correct 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 genuine 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 gird (another convention checker for you).

If however you see double-quotes (“ “) wrapped around the date expression, this suggests either you 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 is the regional setting of the operating system you are using. UK versus USA setting often throw up confusion and inaccuracies to so ensure you are using the correct date/time formats.

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

The post Access Queries: Date Criteria May Not Always Work with MS Access Query appeared first on Access Database Tutorial.