Access 2007-2016 Access 2000-2003 Access Seminars TechHelp Support Tips & Tricks Access Forum Course Index Topic Glossary Insider Circle

 Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us More... What's New? Tips & Tricks Access Index Access Tips Excel Index Excel Tips Waiting List

 < Previous: Access Expert 27 Next: Access Expert 29 >

# Access Expert Level 28

Expert Microsoft Access Tutorial - 2 Hours, 18 Minutes

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 Order Now

If you would like a preview of what's covered in this class,

Access Expert Level 28
 Description: Access Expert Level 28 Versions: 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. Pre-Requisites: 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 fundamentals. Running Time: 2 Hours, 18 Minutes Cost: \$26.99

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

 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 WeekDay() Function WeekDayName() Function MonthName() Function 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 DateAdd() Function 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 DateDiff() Function 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 componentsDatePart() Function Show orders from this yearSimilar as Day, Month, Year Date in a specific monthDate in a specific quarterDate in current week of yearInvalid formulas on Microsoft's site Date in previous weekDate in next weekBe careful of suspicious behaviorUse optional parameters for DatePart Date in previous monthDate in next monthDate in current quarterDate in previous quarterDate in next quarter 05. DateSerial Function (17:03) First Day of MonthLast Day of MonthFirst Day of Previous MonthLast Day of Previous MonthFirst Day of Following MonthLast Day of Following MonthFirst Day of Quarter Last Day of QuarterFirst Day of YearLast Day of Year How Many Days in MonthHow Many Days in QuarterHow Many Days in YearWhat Day of the Year is it?How Many Days Remaining in Year?Exact Age / Anniversary Calculations TimeSerial() like DateSerialDateValue() Function TimeValue() FunctionCDate() 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 OrdinalsLearn how to calculate with IIF Functions 07. Miscellaneous (33:02) Calculate Someone's Next Birthday DateList of Birthdays in Next 60 DaysHow many days until your next birthdayList of Birthdays from Next MonthUse the NZ functionNot all customers have CustomerSinceIf NULL, assume start date of businessField Level Validation RulesTable Level Validation RulesValidation Rule ShipDate > OrderDate Validation Rule OrderDate <= Now() 08. Review (6:36))

Keywords: 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

Student Interaction: 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 applicationReply 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.Thanks,ClariceReply 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 hereExpert 1.

 You may want to read these articles from the 599CD News:
 8/25/2018 NEW: Access Dev 9, 10, 11 8/25/2018 Microsoft Access Developer 11 8/25/2018 Microsoft Access Developer 10 8/25/2018 Microsoft Access Developer 9 8/23/2018 Access Dev 11 is ONLINE 8/17/2018 Access Dev 10 is ONLINE 8/15/2018 Access Tip: Search Form 8/15/2018 Access Tip: Locked v. Enabled 8/15/2018 Access Dev 9 is ONLINE 7/31/2018 Microsoft Access Developer 8

Learn

 Access index Excel index Word index Windows index PowerPoint index Photoshop index Visual Basic index ASP index Seminars More...
Customers

Online Theater
Insider Circle
Student Databases
Change Email
Info

Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Help

Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services

Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order

Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact

Live Chat
General Info
Support Policy
Contact Form
Email Us