Microsoft Access Tutorial - 2 Hours, 18 Minutes
28 is Part 4 of our Comprehensive Guide to Access Functions.
Today's class is part 2 of our focus on Date/Time Functions.
You will learn about many new functions including WeekDay, DateAdd,
DateDiff, DateSerial, DatePart, and more. Topics include:
Date/Time Functions Part 2
- Break Apart
Dates with Day, Month, Year
- Calculate Day of the
Week with WeekDay
- What's 2 months from today?
- DateDiff - How
long until loan paid?
- DatePart - Is order from
DateSerial - Find last day of next
Display Ordinals: 1st, 2nd, 3rd, etc.
someone's exact age
- List of birthdays for next
If you would like a preview of what's covered in this class,
click here to watch the first
and last lessons of
this course (free of charge), or scroll down for more information.
Access Expert Level 28
Access Expert Level 28
Recorded with Access 2013.
Most of the material should work with all versions of Access.
The majority of the functions covered in today's class go all the way back to the
first versions of Access.
Access Expert Level
27 strongly recommended, as you should watch all of my
classes in order. However, if you're skipping around, you should
have at least completed the Beginner series and the first 3 or 4
levels of the Expert series where I start to cover functions.
You should DEFINITELY watch Expert 27, as
that's part 1 of the Date/Time functions and explains the
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:
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,
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
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
00. Intro (8:30)
01. Break Apart Dates (21:59)
Day(), Month(), Year()
Hour(), Minute(), Second()
Is date in current year
Format property display
Is date in previous year
Is date in next year
Is date in current month
Is date in current year to date
What Day of the Week is Today?
First Day of Week
Last Day of Week
Tuesday Following Date
Weeks that Start on a Different Day
What's Weekday if week starts on Monday?
What's Monday before today?
Is Date a Work Day (Mon-Fri)
Is Date a Weekend Day (Sat, Sun)
02. DateAdd Function (9:32)
Query Insert Columns
One day from Date
DateAdd Format Codes
One week from Date
One month from Date
One year from Date
How DateAdd handles leap years
One week before Date
Exactly 9 months from Date
Exactly 21 years before Date
1.5 years from Date
Within one calendar month from Date
Less than one calendar month before
Ten Minutes from Date
03. DateDiff Function (15:32)
Difference Between Two Dates
Number of days between two dates
Number of days since order placed
Months until mortgage is paid
Someone's age (not 100% reliable)
Number of weeks since Jan 1st
Number of minutes worked
First Day of Week Optional Parameter
First Week of Year Optional Parameter
04. DatePart Function (16:52)
Working with individual date components
Show orders from this year
Similar as Day, Month, Year
Date in a specific month
Date in a specific quarter
in current week of year
Invalid formulas on Microsoft's site
Date in previous week
Date in next week
Be careful of
Use optional parameters for DatePart
Date in previous month
Date in next month
Date in current
Date in previous quarter
Date in next quarter
05. DateSerial Function (17:03)
First Day of Month
Last Day of Month
First Day of Previous
Last Day of Previous Month
First Day of Following
Last Day of Following Month
First Day of Quarter
Last Day of Quarter
First Day of Year
Last Day of Year
How Many Days in Month
How Many Days in Quarter
Days in Year
What Day of the Year is it?
How Many Days
Remaining in Year?
Exact Age / Anniversary Calculations
TimeSerial() like DateSerial
CDate() Function - BOTH date AND time
Useful to convert strings to DateTime
06. Ordinal Dates (08:40)
Displaying 1st, 2nd, 3rd, 4th, etc.
Discuss VBA Function for
Learn how to calculate with IIF Functions
07. Miscellaneous (33:02)
Calculate Someone's Next Birthday Date
List of Birthdays in
Next 60 Days
How many days until your next birthday
of Birthdays from Next Month
Use the NZ function
customers have CustomerSince
If NULL, assume start date of
Field Level Validation Rules
Validation Rule ShipDate > OrderDate
Validation Rule OrderDate <= Now()
08. Review (6:36))
Comprehensive Function Guide, Date Time Functions Part 2, microsoft access tutorial, microsoft access tutorial, microsoft access training, access 2007, access 2010, access 2013, Day, Month, Year, Hour, Minute, Second, WeekDay, WeekDayName, MonthName, DateAdd, DateDiff, DateSerial, DatePart, DateValue, TimeValue, CDate
Microsoft Access Expert 28
Richard on 1/19/2015:
Access Expert 28 is Part 4 of our Comprehensive Guide to Access Functions. Today's class is part 2 of our focus on Date/Time Functions. You will learn about many new functions including WeekDay, DateAdd, DateDiff, DateSerial, DatePart, and more. Topics include:
- Date/Time Functions Part 2
- Break Apart Dates with Day, Month, Year
- Calculate Day of the Week with WeekDay
- DateAdd - What's 2 months from today?
- DateDiff - How long until loan paid?
- DatePart - Is order from last quarter?
- DateSerial - Find last day of next month
- Display Ordinals: 1st, 2nd, 3rd, etc.
- Calculate someone's exact age
- List of birthdays for next month
Click here for more information on Access Expert Level 28, including a course outline, sample videos, and more. This course was recorded using Access 2013, but most of the functions covered are valid for all versions of Access. This class follows Expert Level 27. The next class in the series is Expert Level 29.
khadija on 1/19/2015: I'd like to be professional in Access. in every single detail. I know ho to write queries and SQL queries. just I'd like to be pro in Access as an application|
Reply from Alex Hedley:
Well then this is best place to come, work your way through the videos and you'll get there!
Clarice Barkhordarian on 10/8/2015: Hi Rick,|
I am not sure how to get a copy of Code Samples or Date Fuctions. Can you please let me know where I need to look.
Reply from Alex Hedley:
Have you downloaded the Student Database?
vicki Hudson on 1/21/2016: Wow! That was great. Thanks you for the CodeSampleF to refer to. You ROCK. Thanks so much for what you do!|
Bola on 3/20/2016: Hi ,please I a have problem regarding the Access expert level. I have followed the basic level from YouTube, now I want to get the expert level starting from the beginning to end but I am confused from the information from your website which recommended the level 8 and 9 of the basics.|
Reply from Alex Hedley:
The list of courses is here
You may want to read these articles from the 599CD News: