Free Lessons Courses Seminars TechHelp Fast Tips Templates Topic Index Forum ABCD

 Home   Courses   Seminars   Templates   TechHelp   Fast Tips   Forums   Help   Contact   Join   Order   Logon More... What's New? Popular Links ------------ Access Courses Access Index Access TechHelp Access Forum ------------ Excel Courses Excel Index ------------ Code Vault Testimonials

 < 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,
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
 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: \$28.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

 You may want to read these articles from the 599CD News:
 5/16/2022 Referential Integrity 5/15/2022 Invoicing Member Discussion 5/13/2022 Multivalued 5/12/2022 Number Field Size 5/11/2022 Create Word Document 5/11/2022 Export to Word 5/10/2022 On Click Event Timing 5/9/2022 Invalid Use of Null 5/7/2022 Event Handler 5/6/2022 Database Title & Icon

 Learn  Access - index Excel - index Word - index Windows - index PowerPoint - index Photoshop - index Visual Basic - index ASP - index Seminars More... Customers  Login My Account My Courses Lost Password Memberships Student Databases Change Email Info  Latest News New Releases User Forums Topic Glossary Tips & Tricks Search The Site Code Vault Collapse Menus Help  Customer Support Web Site Tour FAQs TechHelp Consulting Services About  Background Testimonials Jobs Affiliate Program Richard Rost Free Lessons Mailing List Order  Video Tutorials Handbooks Memberships Learning Connection Idiot's Guide to Excel Volume Discounts Payment Info Shipping Terms of Sale Contact  Contact Info Support Policy Email Richard Mailing Address Phone Number Fax Number Course Survey