We will be undergoing scheduled maintenance on May 20th, 2013 at 02:00 GMT.
Microsoft Access 2010 Tutorials
Working with Tables / Custom Date/Time Formats
Visitors to VTC.com will be able to view all introductory videos for each training course.
Free Trial Members will gain access to first three chapters for each training course.
Full Access Members have full access to VTC.com�s entire library of video tutorials.
Learn More
Subtitles of the Movie (Microsoft Access 2010 / Working with Tables / Custom Date/Time Formats)
Custom Date-Time Formats are, a lot of times, kind of a best kept secret for a lot of Access developers. And this really can do some cool things for the data that you capture, the way you report these things, and so forth. You can create your own custom formats, and the way you do this is just enter the format in that Format Setting in the Design View, and we'll jump into Access in just a few minutes here and I'll show you exactly how that works. But what I want to show you is, is that if I put a d down there in that Design View format field, it will take whatever date I put in. I can just put in, you know, 4 17 56 and it is going to store the number 17 in the database, the day of the month. If I put two d's in there it will show the day of the month in two numeric digits. In other words, if I put April 2nd, it would show it as 02 as the day. Ddd, the first three letters of the weekday - now this is really cool because I can take any date and figure out what actual day of the week that is. Four d's will give me the full name of the weekday. A single w, lowercase w, gives me the day of the week, 1 to 7, and you can go in and set up whether Sunday's the first day of your week or Monday, and that sort of thing. We won't get into that right now. Week of the year, ww. Then we'll just continue looking at some of these. M, lowercase m, will show me the month of the year in one or two numeric digits. Mm always shows it in two numeric digits. Mmm, the first three letters of the month. Four m's - you see a pattern here, right? - the full name of the month. Q. This one's really cool for reporting - the quarter of the year. Y is the number of the day of the year and then we can do yy, the last two digits of the year. Four y's gives us the full year from the year 100 to the year 9999. And so you can see, you've got a lot of power here that we can use. So let's jump out. So, notice in Access now, on the Order Date Field Name, I have Date-Time but I have General Date. Well, let's go out and actually look at this table. So, I will double-click on it. Now I have to save the table. Yes. Now, notice when I click on this, I get the little calendar pop-up and if I choose April 21st and then I click somewhere else, it puts April 21st, 4-21-11, 2011, in the database there, alright? So, if I jump back out into Design View and I change this guy to let's say Medium Date, and open the table - I have to save it again - notice that it has now changed it to that. So, whatever I choose, it's changing it to that format. When I click off of it is when I'll actually see the formatting hit, OK? Now let's go talk about these custom situations. I'll go back into Design View and this time, right here, I want to see just the quarter of the year, alright? What quarter of the year is this date? And so I'll double-click on that table, jump back out there, and notice April is in, anywhere in April, is going to say number 2. Now, if I click out here and go, say into Set October, say October 20th, and click away, notice I'm in the fourth quarter now. Now obviously, I would need to change the title of my column here to Quarter, right? But you notice what you can start to do with these dates, and you can build some cool functionalities to grab these things. But if you need to capture this data this way you can really do some neat stuff. Now, go play with some of those formats that I set up for you and you can see that you can really build some cool formats out here. And just if you put an m slash, you know, dd slash yy, you can set up the format however you would like it out there in that Format area down here, just by typing those letters in. So, a lot of cool things that you can capture and put into that database without anybody even having to think. You can just put a quarter column, let them choose the date, and it'll automatically figure out the quarter, the day of the year, the number of the day of the week - you can start to get some really cool metrics out of this thing.
Tutorial Information
| Course: | Microsoft Access 2010 |
| Author: | Mark Long |
| SKU: | 34224 |
| ISBN: | 1-936334-91-7 |
| Release Date: | 2011-05-12 |
| Duration: | 9 hrs / 121 lessons |
| Work Files: |
Yes |
| Captions: | No |
| Compatibility: |
Vista/XP/2000, OS X, Linux QuickTime 7, Flash 8 |
VTC Sign up & Benefits
- Unlimited Access
- 81,350 Video Tutorials (14,200 free)
- Video Available as Flash or QuickTime
- Over 715 Courses
- $30 for One Month Access
- Multi-User Discounts Available
