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.
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. These are the queries that require a date range such as within the last 30 days or 2 weeks from today. The first part requires us to accurately define today's date. In the next movie, we'll take a look at how we can combine this with some additional functions to perform some calculations to define time span. Let's start a new query in Design view.
Create, Query Design, and I'll add our Orders table. Go ahead and say Close. Instead of building an actual query, I'll use the fields here to demonstrate the Date, Time, and Now functions. The Date function is written like this, Date(). This tells Access to fetch the current date. In the next field I'll write out Time followed by an open and closing parentheses. This tells Access to fetch the current time. In the third field, I'll write Now followed by open and closing parentheses.
This tells Access to get the current date and time and combine them in one field. Let's go ahead and run this query. I'll expand the fields and you'll see that Access has returned the current date and time that I'm recording this movie. Today is May 17th, 2011 at 3:33:48. The third column, the Now expression, takes our Date and Time field and combines them into one field. Now, let's take a look at how we could use the Format function to format our date. Let's go back into Design view and I'll highlight these two fields and press Delete.
Now I want to use the Format function to write a statement that will format our date. Let me expand this open a little bit and we'll start typing. The Format function starts with Format and an open parenthesis. The first thing we need to supply is the date that we want to format. In the movie on the Format function we took the OrderDate from the Orders table. Here we just want to format the current date. So we'll supply the Date function. Date(). The second part we need to supply is how we want it to be formatted. We can use a comma and a quotation mark to tell it that we're moving onto the next piece and then let's format it like this, m.d.yy.
We'll finish our statement with a closing quotation mark and a closing parenthesis. Now, if I run this query, we'll see that the Format function is taking today's current date and it's reformatting it in the way that I specified. So now that we can accurately define the current date, we can use this functionality to help us define date ranges relative to today. We'll take a look at that in the next movie.
There are currently no FAQs about Access 2010: Queries in Depth.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.