Access Queries: Date Criteria May Not Always Work with MS Access Query

1
Access Queries Date Criteria May Not
Always Work with Your Access Query
2
Learning about Microsoft Access queries 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 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 query below
showing orders before the year 2016 and the
design of the query which suggested a date range
from 1st January 2016 to 31st December 2016.
3
The criteria for the above shows gt01/01/2016 lt
31/12/2016 which will not actually return the
correct range and instead show whats known as
logical errors (dates outside the range).
  Its 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 its
the right data type too (date/time in this case).
4
A revised example is shown below
5
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 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.
6
Contact us
Ben Beitler
ben_at_accessdatabasetutorial.com
(44) 7881
502400
United Kingdom
London
https//www.accessdatabasetutorial.com/