Today, I thought I’d write up a small post on one of the most common huddles that new developers face which is building proper query criteria.
The issue most novice developers have is the simple fact that they don’t yet know the fact that they have to adjust each criterion based on the field data type they are applying the criteria to. Thus, you cannot treat all criteria the same!
So I thought I’d break down the basics and show a few simple examples.
Main Data Types of Concern
When writing queries in Access you need to differentiate the fields by their data type (from the table design) and break them down into 3 categories:
- Numeric
- Text
- Date/Time
and then build each criteria accordingly.
Numeric
Numeric fields are the easiest to deal with because there is nothing special to do. They simple accept the criteria ‘as is’.
WHERE [OrderQty] = 14
or
WHERE [Age] > 55
or
WHERE [ID] In (121, 457, 739)
String/Text
Text data types need to be surrounded by quotes to be used as a criteria.
WHERE [FirstName] = "George"
or
WHERE [City] = 'New York'
Notice the 2 examples use different quotations (single vs. double). Both are acceptable, but I privilege single simply because it also works in VBA ‘as is’. Double quotes when ported to VBA requires special handling with doubling the double quotes and can quickly become unruly.
Furthermore, when using SSMS with SQL Azure, … double quotes are invalid and you need to use single quote, so perhaps that is another good reason to simply get used to always using single quotes.
Date/Time
Date time fields expect to have values surrounded by the # symbol, but things get even more complicated. To avoid issues, dates should be formatted in the American style m/d/yyyy. This is done so Access properly recognizes months from days, … otherwise it may inverse values giving you incorrect data, calculations, …
WHERE [StartDate] = #12/22/2017#
or
WHERE [ClientAppointment] = #7/14/2020 08:45#
This is also why I long ago adopted to use Allen Browne’s SQLDate function to massage my date into the right format regardless of regional settings…, especially when building queries in VBA!
Other examples of Date clauses include things like
WHERE [StartDate] BETWEEN #12/22/2017# AND #12/31/2017#
WHERE [StartDate] > #12/22/2017# AND [StartDate] < #12/31/2017#
WildCards
It's important to note that Access has a series of wildcard symbols you can use to make criteria more flexible. Now this is a HUGE subject, so I'm just going to touch on the very basic and most common wildcard here.
The most common would be the * which simply represents 'anything'.
WHERE [FirstName] Like 'Da*'
which would return things like Daniel, David, Dan, ... anything that starts with Da regardless of what comes after that.
You can use the * wildcard anywhere you'd like, start, end, middle and you can use it more that once within the same criteria.
WHERE [FirstName] Like 'Adr*n'
might return things like Adrian, Adrien
Do note that wildcard search are typically some of the worst performing searches, so only use them when you truly have to and use specific searches whenever possible.