DATE CRITERIA MAY NOT ALWAYS WORK WITH MS ACCESS QUERY

1
DATE CRITERIA MAY NOT ALWAYS WORK WITH MS ACCESS
QUERY 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.
2
The criterion 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 hand will capture the
correct range too. The (hash sign) is the
exact convention and good check to ensure its
the right data type too (date/time in this
case). A revised example is shown below
3
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/