Happy 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.
Enjoy.