So far we've taken a look at how to work with dates relative to each other or relative to a specific and static date range. Access provides a couple of additional functions that will allow us to define dates relative to today or relative to right now. This includes all of the queries that require a date range such as within the last 30 days or two weeks from today. In other words, dates for the specific relationship to the current date and time. The first part requires us to accurately define today’s date and time. In the next movie, we’ll take a look at how we can combine this with some of the additional functions, to perform some calculations to define time span.
So let’s first start with creating a new query in design view. We’ll go to the create tab, and press query design. Here, I'm going to add in our employees table and say close. Now I'm not actually going to use any fields from the employees table. So I'm just going to come down here into the very first column, and I'm going to type in a function here. The function I want is called date. I'm just going to type in the word date, and an opening parenthesis, and a closing parenthesis. The date function does not require any arguments. So if I press Enter, you'll see that it finishes it off. Now Access apply this EXPR1 alias here at the beginning.
Let's go ahead and highlight this, and i'm going to rename this to today. This is going to return today's date. Lets go ahead and click Run, and you'll see that today's date is June 5th, 2014. Let's go ahead and go back into Design view. I'm going to go to the next column over, and I'm going to type in a different function. This time the function I want is called Time. I'm going to type in the word time here as the alias. So I'll type in a colon, and then I'll type in the name of the function, time, an open parenthesis and a closing parenthesis. Again, this doesn't require any arguments. I'm going to go ahead and press the Run button.
And we'll see that the current time is 12:40 and 48 seconds. These queries are automatically updating every time you run or refresh the query. So for instance if I refresh the query here, you'll see that the time updates to 59 seconds. If we waited long enough, we'd see that the date rolls over as well. Let's go back into design view. There's one more function that I want to take a look at. When I come into this third column over here, and I'm going to type in the alias now, a colon, and the function is called now as well. So I'll just type in now and open parenthesis and a closed parenthesis.
Again it doesn't require any arguments. Let's go ahead and press the run button. Now I need to expand this column a little bit, and we'll see that the now function returns the date and the time in a single field. So we can combine the date, time, and now functions with a format function that we saw earlier. Let's go ahead and test that out. Let's go ahead and go back into design view. I'm going to go into the fourth column over here, and I'll right-click and say build. I'm going to go into my functions group, into the built-in functions, I'm going to scroll to the bottom to the text group, and then I'm going to find that format function we've used previously.
I'll double-click on it to add it up here. Now, once again, I'm going to get rid of the last two optional arguments, so I'll highlight all of this and press Delete. And that'll just leave the expression and the format. Now in this case, we're not going to feed it a date out of our database. What I want to format here is today's date. So I can just nest in a function inside of this Format function. So I'm going to write out Date, an open parenthesis and a closed parenthesis. So what we're going to be formatting is today's date. The format here, we can type in the string. I'm going to use the double quotes, and I'm going to write out four d's, a space, four m's, a space, and four y's.
I'll finish that off with a closing quotation mark. Let's go ahead and say OK. And we'll run the query to see the results. And if I expand this out you can see that today is in fact Thursday, June, and 2014. So now that we can accurately define the current date and time, we can use this functionality to help us define date ranges relative to today's date. We'll take a look at that in the next movie.
- 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