Specifying a range of dates or times
Video: Specifying a range of dates or timesBecause Access stores dates as serial numbers, it becomes very easy to perform date comparisons or define ranges of dates. We've seen these comparison operators before, but I thought it would be valuable to review them and how they can be applied specifically to working with dates. We'll create a new query in Design view and we'll add a couple of tables. Let's grab our Customers table and our Orders table. Go ahead and close Show Table and we'll add some fields to our query. Let's add First and LastName and from the table Orders, we'll add OrderDate.
- Next Steps
Viewers: in countries Watching now:
In this course, author Adam Wilbert illustrates how to create and leverage real-world queries and turn raw data into usable information. The course covers setting up queries, performing calculations, using the built-in Access functions to further refine query results, and identifying top performers or areas for improvement based on a range of criteria.
- Naming conventions and best practices
- Working with joins
- Using comparison operators
- Defining criteria for select queries
- Creating parameter queries
- Creating calculated fields
- Working with dates and times
- Using the Expression Builder
- Creating conditional statements
- Making, deleting and appending records
- Building reports
Specifying a range of dates or times
Because Access stores dates as serial numbers, it becomes very easy to perform date comparisons or define ranges of dates. We've seen these comparison operators before, but I thought it would be valuable to review them and how they can be applied specifically to working with dates. We'll create a new query in Design view and we'll add a couple of tables. Let's grab our Customers table and our Orders table. Go ahead and close Show Table and we'll add some fields to our query. Let's add First and LastName and from the table Orders, we'll add OrderDate.
Now in our Criteria section, we can specify a date. Let's say we are only interested in records from May 12, 2010. 05/12/10. Let's go ahead and run this query and we get the two records that happened on that day. Back in Design view as you'll notice that Access wrapped our date around date delimiters or these hash-marks. Now Access is really flexible when it comes to dates. We can enter them in a variety of ways and it'll understand all of them. We can enter a 5-12-2010 and Access will understand that that's exactly the same thing.
If we go back to Design view, we can even type a date like this. May 12, 2010, and again Access says that's exactly the same thing. It understands all of those different formats. Let's go back into Design view. Now we can use comparison operators with our dates as well. For instance, if we're interested in all the records that happened after May 12th, we can use the greater than symbol, greater than May 12th, 2010. We'll run that and we'll see all the records that happened after May 12th.
Let's go back to Design view. we could also say greater than or equal to May 12th. This time it will return dates including May 12th and everything after. Let's go back to Design view and we'll apply some logical operators to our criteria. Let me go ahead and expand this a little bit so I have more room. After my statement greater than or equal to May 12, 2010, let's add another date to specify a range. And less than or equal to 5/30/2010.
Now we'll get a range of dates. Go ahead and run it. We'll see we have 21 records within that range. Notice that we're getting May 12 and May 30. So the greater than or equal to is inclusive. Let's go ahead and go back to Design view. Now rather than applying greater than or less than with dates, there are two functions that we can use specific for dates. That would be a Between statement. Here we can write Between 5/12 and 5/30. If we run that, we'll see we get the exact same number of records, 21, and it includes May 12 and May 30.
Finally, let's look at how we can apply a parameter request with dates. Let's go back into Design view and instead of supplying the dates hard-coded into our query, let's make this a parameter request so that the end user can supply the dates that they're interested in. We'll write it like this, Between, square bracket, Enter Start, closing square bracket, and then we'll write Enter End in square brackets.
to define the range. If we run this query, we'll get our Enter Parameter Value box where we can enter the start date. Remember, we can enter this in any format. I'll just say 5-12-10. Press Enter and I get the second box to ask me for the end date. Let's go ahead and enter this differently. May 30, 2010. Access returns the same records. So using dates and date ranges in a query's criteria field is a common way to return the most significant data or to limit the returns to only the most recent and relevant time periods.
There are currently no FAQs about Access 2010: Queries in Depth.