Access 2007-2019
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
Home   Courses   Seminars   Templates   Help   TechHelp   Forums   Contact   Join   Order   Logon  
Access X28 and Advanced DateTime Seminar
By Richard Rost   Richard Rost on Twitter Richard Rost on LinkedIn Email Richard Rost   6 years ago

Microsoft Access Expert 28Happy 2015, everyone! Sorry that I didn't post much during December. The holidays kept me quite busy with both some work projects and family. But, January has a LOT of stuff coming, so hold on to your hats.

I just released Access Expert 27. This was originally going to just be one class, but it turned out to be over 3 hours of material, so I broke it up into two. X27 is available now on the web site. I've got all but one of the lessons finished for X28 (I decided to add one more thing) and then I'll release that in a couple of days.

X27 covers a lot of good, solid DateTime fundamentals. Some of it we've learned in earlier classes, but I really just skimmed the surface of working with dates previous to this. You get a lot more detail on how dates and times work. Check out the X27 outline for details.

X28 should be available in a couple of days. This covers mostly NEW material, including functions you've never seen before in my classes: WeekDay, DateAdd, DateDiff, DatePart, DateSerial, and more. We'll see how to do things like figure out the last day of next quarter, the first day of last month, how many days are in February of 2016, and so on. I'll post the full outline of everything I've covered so far below just so you can look it over.

Now, a few weeks ago when I told everyone that I would be covering dates and times in X27, you posted some things you wanted to see in the Forums. I tried to include as many of the things as I could in these two classes, but some of them require VBA programming, which I'm not including in the Expert series. So, I'm going to put together a short seminar with these solutions in it. I'll call this the Advanced DateTime Seminar and it's going to include:

- Calculating the number of WORK days between two dates (excluding holidays and weekends)
- Updating a timestamp when a record is EDITED (adding is easy, EDITED not so much)
- A to-do list with buttons to move to the future (30 mins, 1 day, 1 month, etc.)
- A "popup" reminder for that to-do list (you have an appointment in 30 minutes)
- Some advanced calculations like "how many Mondays are left this month?"

If you can think of anything else you'd like to see covered in this seminar, then please let me know. Email me or post it here in the Forum as a comment below. I'll add as much stuff as I can.

I expect to have X28 ready in the next couple of days, and then the Seminar a few days after that. I have some other projects I'm working on too, concurrently, so I'll do my best to get these out ASAP. For those of you on the Waiting List for X27, you should have already received your copy. Even though I plan to release X28 on the web site in the next couple of days I'm going to wait until next week to process the Waiting List orders for it, to give you time to finish X27 before the next class comes at you. If you don't want to wait, feel free to order it online (you won't get a 2nd copy) or email customer service and we'll send it to you.

OK, here's what I have recorded so far for the NEXT class (X28). These videos are FINISHED.

01. Break Apart Dates ()
Day(), Month(), Year()
Hour(), Minute(), Second()
Is date in current year
Format property display -1/0 as True/False or Yes/No
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 (like Monday)
What's today's Weekday if week starts on Monday?
What's the date of the Monday before today?
Is Date a Work Day (Mon-Fri)
Is Date a Weekend Day (Sat, Sun)

02. DateAdd Function (9:22)
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 Date
Ten Minutes from Date

03. DateDiff Function (15:31)
Calculating Difference Between Two Dates
DateDiff() Function
Number of days between two dates
Number of days since order placed
Number of months until mortgage is paid
Someone's age (not 100% reliable)
Number of weeks since Jan 1st of current year
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
DatePart() Function
Show orders from this year
Similar things with DatePart as with the Day, Month, Year functions
Date in a specific month
Date in a specific quarter
Date in current week of year
Invalid formulas on Microsoft's web site
Date in previous week
Date in next week
Be careful of suspicious behavior around the end of the year
Use optional parameters for DatePart
Date in previous month
Date in next month
Date in current quarter
Date in previous quarter
Date in next quarter

05. DateSerial() Function
First Day of Month
Last Day of Month
First Day of Previous Month
Last Day of Previous Month
First Day of Following Month
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
How Many Days in Year
What Day of the Year is it?
How Many Days Remaining in Year?
Exact Age / Anniversary Calculations
TimeSerial() Function Just like DateSerial
DateValue() Function
TimeValue() Function
CDate() Function - BOTH date AND time
Useful to convert strings to DateTime values

I still have one more video to record (plus the bookends). I'm going to cover a few more examples, including some sample projects. Here's what I have planned (not set in stone):

- List of birthdays in the next 60 days
- List of birthdays in the next calendar month
- Update query: mark birthday card "sent"
- List of appointments for the week with buttons to move "forward" and "back" one week at a time
- List of recurring appointments. Mark an appointment FINISHED and it resets for a future date
- Use validation rules to prevent ShipDate being before OrderDate

Again, if you have any ideas, let me know.


Access X28 and Advanced DateTime Seminar Upload Images   Link 
Indy Puaar 
6 years ago
Hi Rick,
One problem i have with dates in access is finding records within a date range. For example:
A  01/01/2015  01/02/2015
B  02/02/2015  01/03/2015
Add a Reply
Access X28 and Advanced DateTime Seminar Upload Images   Link 
Chris Bezant 
6 years ago
Hello Rick
The only thing that springs to mind for the dates seminar is a feature I have in a very crude 'Reminder' database that I have. There are some things that I really need a day by day count down to, such as the wife's birthday so that I panic as it closer and I have failed to do something!!! Read More...
Add a Reply

Start a NEW Conversation
Only students may post right now. Click here for more information on how you can set up an account. If you are a student, please LOG ON first.

You may want to read these articles from the 599CD News:

7/27/2021Quick Queries #5
7/27/2021Loop Thru Fields in Table
7/26/2021First Monday
7/25/2021Missing Months
7/24/2021Center Vertically
7/22/2021Buy Access
7/21/202164-Bit Access
7/18/2021Splash Screen

Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Account Login
Online Theater
Lost Password
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Customer Support
Web Site Tour
Consulting Services
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Contact Info
Support Policy
Email Richard
Mailing Address
Phone Number
Fax Number
Course Survey
Blog RSS Feed    Twitter

YouTube Channel    LinkedIn