Join Adam Wilbert for an in-depth discussion in this video Specifying a range of dates or times, part of Access 2013: Queries in Depth.
- View Offline
Because Access stores dates as serial numbers, it becomes very easy to perform date comparison or to find ranges of dates. We've seen these comparison operators before, but I thought it'd be valuable to review them and see how they can be applied specifically to working with dates. For this movie, let's take a look at exploring our employees' hire dates. I'm going to start a new query in Design View. And I'm going to go ahead and add in my Employees table. We'll go ahead and close the Show Table window. From the Employees table, let's add the Employee ID, the first and last name, and I'm going to scroll down here, and choose the hire date.
Now, let's make sure that we're sorting our hire dates, so I'm going to choose to sort them ascending, so they go from the oldest to the newest. And we can go ahead and run the query to see our 741 employees and the dates that they were hired. Let's go back into Design View. Now we can filter our results to a specific hire date, just like we can with any other criteria. I just find the criteria row in this Query Design grid, and underneath Hire Date, I type in the date that I'm interested in. Let's say that we wanted to find all of the employees that were hired on May 15th, 2014. So I'll come down here and I'll just type in 5/15/2014.
When I press Enter, Access automatically wraps around these hash or pound symbols. These are equivalent to the quotation marks that appear around text fields. Here, the pound symbol is just telling Access this is a date, not the formula, 5 divided by 15 divided by 2014. Now that we have our date criteria in, let's go ahead and run the query. And we can see that it filters to just the two employees that were hired on May 15th. Let's go back into Design View here. Now I'm going to edit this criteria a little bit. Access is actually really smart about how you can enter in dates.
For instance, if I typed in the word May 15, 2014 notice that when I press Enter, Access converts it to the numerical values. I can I also type it in with different separators, so if I type in 5-15-14, Access still understands that, that's the exact same value. So that's how we specify a single criteria. But what if we wanted to specify a range of dates? For instance, what if I wanted to find all of the employees that were hired since 2012? Well I can go into here in this criteria row and we will highlight this and get rid of it, and we can use some of our comparison operators that we saw earlier.
I'm going to type in the > symbol, and then type in 1/1/2012. Go ahead and press Enter and Access wraps those date delimiters around it. Let's go ahead and and run this query. Now we can see that we have 170 employees that were hired since 2012. Now there is one small problem with this. Let's go back into Design View. In this criteria here I specified that I wanted everything that was greater than 1/1/2012. What I actually typically probably wanted was all of the dates were greater than or equal to 1/1/2012.
If I type in greater than or equal to and run the query again, you'll notice that I actually had one employee that didn't show up on the original query. So when filtering your date based criteria, you need to make sure whether you're literally looking for something that is greater than or looking for something greater than or equal to. That's a really common mistake to make. Let's go back into Design View. Now we can also specify ranges of dates. So for instance, if I wanted to find everybody that was just in 2012, not since 2012. We can come back down here into this criteria and let me make this column a little bit wider here, and I can specify a range of dates by specifying and, and then the closing date.
Let's go ahead and say, less than or equal to, December 31, 2012, so 12/31/2012. Again I press Enter and Access wraps around the pound symbols. Let's go ahead and run this and we will see all of the employees. There's 94 of them that were hired in 2012. Now there is one other way that we can specify ranges of dates and it's a little bit easier to read. Let's go back into Design View. I am going to highlight all of this and delete. Instead of typing in greater than or equal to and less than or equal to, we can use the words between the starting date, let's say 1/1/2012, and the closing date, 12/31/2012.
The between and syntax is inclusive, so if I run this query, we'll get exactly the same results, we have 94 employees, including Michael Harvey, that was hired on January 1st, 2012. So using dates and date ranges in the query's criteria field is a very common way to return the most significant data, or to limit the returns to only the most recent and relevant time periods.
- Defining criteria
- Understanding comparison operators
- Using joins
- Creating parameter queries
- Using Expression Builder to work with functions
- Working with dates and times
- Creating conditional statements
- Finding duplicate records
- Creating backups
- Making, deleting, and appending records
- Understanding SQL basics and writing SQL queries
- Useful query tricks