Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
There are thousands of ways that dates can be valuable in your database and just as many variations on how they're formatted or displayed. They can be written longhand for use on business letters or abbreviated for use on payroll checks. They can be incorporated into serial, production, or batch ID numbers, or used for quarterly financial reporting. Because Access stores dates as serial numbers as we saw in the previous movie we have an infinite number of ways that we can display our data. One of the ways that we can do that is with the Format function. The Format syntax looks like this.
It requires two pieces. It requires the expression that we want to format and this is where we want to plug in our DateField, and it requires some symbols. For the symbols we have a variety of options. We'll use a lowercase d to represent days, m represents months, y represents years. We can also use q for quarters, wd for weekdays, and ww for weeks of the year. We'll use these symbols in a variety of configurations. If we write one d that tells Access that we want to return a single digit or double digit day of the month.
If we use two ds, that tells Access that we wanted double digit day of the month. So for instance days 1 through 9 we'll get a leading 0. If we use three ds, that tells Access we want returned a three letter abbreviation for the week and if we use four ds, that tells Access that we want to return the full name of the day of the week. Months work the same way. We have the option to return a single or double digit month of the year, a three letter abbreviation, or the full name of the month. With years we have two options.
We can either return a two digit or a four digit year. So let's see how we can combine these symbols into various configurations to get different date formats. If we format our date like m-d-yy, we'll get a date that has a one digit month, a one digit day, and a two digit year separated by hyphens. If we use two mm/dd/yyyy, we'll get a two digit month, a slash, two digit day, a slash, and a four digit year.
Let's look at another. How about mmm, yyyy? This will return a three letter abbreviation for the month and the four digit year, separated by a comma. One more here dd mmmm, yyyy. We'll get a two-digit day, the full name of the month, and the four digit year. Let's jump into Access and we'll use the Expression Builder to build a couple of these functions. We'll start with the Create tab and go into Query Design. Next, we'll add our Orders table. Let's add our OrderID and the OrderDate field.
In the third field we'll right-click and select the Builder. So let's start typing out our Format function. We'll write the word Format(. Access's pop-up help here will tell us that the next thing we need to supply is an expression. This is the date that we want to format. Let's drill into our database to find it. We'll going into the TwoTrees database into the Tables folder and into our Orders table. Here we could double-click and select the OrderDate. Now we need to supply the symbols to help us format it.
We'll add a comma and a quotation mark and then we'll put in our symbols. So let's try m.d.yy. We'll add a closing quote and a closing parenthesis to finish the Format statement. Let's go ahead and say OK and run our query. Access has formatted the dates for our specifications using the symbols. We have a period as a separator. Let's go back to our Design view and let's expand this out. We'll see that Access has actually modified our code a little bit by putting these slashes in.
Now the slashes mean that the next character is literally what we want. The period has special meaning in SQL, so Access put the slashes in here to specify that we want to actually include a period and this isn't mean something different in SQL view. Let's go ahead and highlight this out and we'll change our function a little. Let's be really verbose now. dddd, mmmm dd, yyyy. Let's see what this does.
Go ahead and click Run and we'll expand this out. We'll see that Access returns a date like Monday, January 10, 2005. So as you can see, the Format function can be pretty flexible at how you display dates and can satisfy almost any reporting need that you may require.
Get unlimited access to all courses for just $25/month.Become a member
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.
Your file was successfully uploaded.