Access Expert Level 28

This is part 2 of the DateTime functions, and part 4 of the Comprehensive Guide to Access Functions. We will be learning many new functions in today's class. We will start by using these simple date/time functions to break apart a date value into it's components:

- Day()
- Month()
- Year()
- Hour()
- Minute()
- Second()

We can use these functions to answer questions like is this date in the current year? Previous year? Current month? Year to date? We will use the WeekDay() function to determine what day of the week a particular date falls on (Sunday thru Saturday). We can then determine if this is a work day (M-F) or a weekend day (S,S).

Next we will learn about the DateAdd() function which we can use to add any interval to a date range. The interval can be days, weeks, months, years, quarters, etc. This is handy for working with whole calendar months or years. You can say "add 3 months to this date" and get the correct answer; much better than just adding 30 days for a month. You can also subtract dates, such as "what date was exactly 7 year ago?" or "what date is 9 months from now?" In this example, we are adding 10 years to the value in field D:

We will learn about the DateDiff() function which is used for finding the difference between two days, again in any interval you want. How many years are between two dates? How many weeks? How many whole months? How many weeks have there been since Jan 1st of the current year? We'll also learn about the optional First Day Of Week and First Week of Year parameters.

The DatePart() function is like the functions we learned earlier for taking a date apart into it's various components, but it has a lot more flexibility. We can use DatePart for answering questions like "is a date in a specific month?" or "is this date in the previous quarter?" I'll show you a couple of formulas listed on Microsoft's site that may not give you correct values when dealing with weeks and the DatePart function, and my replacements for those.

The DateSerial() function is one of the most useful functions in Access. You can use it to construct a date from its various components. You can use DateSerial to find the first day of the month, the first day of next month, the last day of the previous quarter, how many days are in this year, and lots more. We'll learn how to calculate someone's EXACT age (to the second). This is great for birthdays, anniversaries, and knowing whether or not someone should be drinking in your bar. :)

You will learn how to display Ordinal Dates, so if you want to say "Today is Monday, February 2nd" you can. You'll learn how to use nested IIF functions to determine how to write 1st, 2nd, 3rd, 4th, etc.

Finally, you will learn how to generate a list of all of the birthdays (or anniversaries) coming up next month, so you can print out birthday cards in January for all of the February birthdays. We'll also talk about Table-Level Validation Rules, so you can make sure all of your order ship dates are later than the order dates.

New with this class, all of the functions and other code used in the class will be included in a "Code Sample" form inside the database you can download from my site at no extra charge:

This is the 28th class in the Access Expert series. This is the fourth class in my Comprehensive Function Guide series, and part 2 of 2 classes on Date/Time Functions. If you're serious about building quality databases with Access, don't miss out on this course. Of course, if you have any questions about whether or not this class is for you, please contact me.
 

Complete Outline - Access Expert Level 28