ERROR #3704: Operation is not allowed when the object is closed. Microsoft Access Expert Level 27 Tutorial Comprehensive Function Guide, Date Time Functions
 

4/24/2017: You may see an "operation not allowed" error on the site. We're working on fixing the problem. Nothing seems to be affected, it's just annoying. Carry on. :)   [dismiss]
 
Access 2007-2013
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  
 
NEW Release: Access Advanced Level 4 and Level 5   dismiss
 
 

< Previous: Access Expert 26

Next: Access Expert 28 >

Access Expert Level 27

Expert Microsoft Access Tutorial - 1 Hour, 39 Minutes
 
 
Access Expert 27 is Part 3 of our Comprehensive Guide to Access Functions. Today's class is part 1 of our focus on Date/Time Functions. You will learn some additional tricks with the Date, Time, Now, and Format functions. We will build an Aged Accounts Receivable, and we will learn more about date/time math operations. Topics include:
 
access cd   - Date/Time Functions Part 1
  - More with Now, Date, Time Functions
  - Query Data in a Variety of Date Ranges

  - Date & Time Mathematics
  - Two Digit Year "2030" Problem
  - List of Upcoming Birthdays
  -
Aged Accounts Receivable Report
  - Display Times as 8:30, 8.5, 8h 30m
  - Timesheets Spanning Midnight
  - Calculating Someone's Approximate Age

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 27
Description: Access Expert Level 27
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 26 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.
Running Time: 1 Hour, 39 Minutes
Cost: $26.99


We have covered some of these functions in previous classes, however in this Comprehensive Guide to Access Functions we will cover those in a lot more detail, plus learn many new functions. Today's class is part 1 of 2 classes covering Date/Time functions. We will begin by learning some keyboard shortcut tricks to add automatic dates and times to our database during data entry. We'll spend some time learning more about the Date(), Time(), and Now() functions. We'll see how to use these functions to create default values in our tables and forms.

date time now part 1

 

We'll learn more about the Format() function's parameters. We'll discuss the "2030" two-digit year problem and how to fix it. We'll learn how to create queries to show us:

- Records from today
- Records in the past or future
- Records on or not on a specific date
- Records between two dates
- Records outside of a date range
- Records in a specific list of dates, using the IN() function

 

We will learn a lot more about Date/Time Mathematics and the specifics of how to add, subtract, and deal with date and time values. We'll learn why the date 12/30/1899 is significant to Access and Excel users. We will learn how to use date math and the IIF() Function to create an Aged Accounts Receivable where we can see all of our past due, unpaid orders, and which ones are current, 30 days late, 60 days late, or more.

aged accounts receivable

 

We will take extra special care to address date values that have times with them. These can often be a problem with many date calculations. You will learn how to show:

- Records from today with times in them
- Records between two dates with times
- Records from any specific date or range of dates with times
- Records for tomorrow or yesterday
- Birthdays from now until next week
- Orders less than one week old
- The number of days late an order is

 

Next we will learn how to work more with the mathematics of time values. You'll learn more about calculating hours and minutes as a fraction of a day. You'll learn how to calculate one hour in the future, or five minutes in the past. You'll use the CDate() function to convert other types of values into dates. You'll learn how to calculate someone's approximate age using date math. We'll work with a timesheet that has time values spanning midnight, allowing you to calculate the proper number of hours worked if someone started their shift at 11pm and ended at 6am the following day. You'll see how display 8.5 hours as 8:30, or 8h 30m. You'll see how to display 24:00 instead of 00:00, and format with one or two digits for hours and minutes.

date time worksheet

 

This is the 27th class in the Access Expert series. This is the third class in my Comprehensive Function Guide series, and part 1 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 27

00. Intro (5:46)

01. Date Time Now 1 (24:49)

Inserting DateTime Shortcut Keys
Insert Date
Insert Time
Insert Date from Previous Record
Date Time Functions
Date(), Time(), Now() Functions
Display Current Date in Unbound Form Field
Use as Default Value in a Table Field
Use as Default Value in a Form Field
Format Function Parameters
Show Date as "Monday, December 15, 2014"
Two Digit Year Cutoff - 2030 Problem
Automatically Timestamp New Records
Records From Today
Records in the Past
Records in the Future
Records on a Specific Date
Records NOT on a Specific Date
Records Between Two Dates
Records NOT Between Two Dates
Records Outside of a Date Range
Records on Specific Dates with IN()
02. Date Time Now 2 (37:17)
DateTime Mathematics
Date Stored as a Number
Number of Days Since 12/30/1899
Good Enough for Most Circumstances
Watch for Dates with Times with Between
Records From Today With Times
Records Between Two Dates With Times
Records on a Specific Date With Times
Records For Tomorrow
Records from Yesterday
Records Within one week from today
Birthdays From Now until Next Week
Orders Less than One Week Old
Aged Accounts Receivable
Query with Orders, OrderDetailQ, CustomerT
Need DueDate, CompanyName, OrderTotal
Order must be an Invoice and Not Paid
Aggregate Totals Group By ID, SUM Total
Put Aging in Second Query (for clarity)
Use IIF Function to Add Aging Data
Current Orders
Less than 30 days old
Between 30 and 60 days old
More than 60 days ago
Number of Days Late
Accounts Receivable Report
Conditional Formatting to Hide Zero
Totals in Report Footer

03. Date Time Now 3 (25:24)
Hours as Fractions of a Day
One Hour in the Future
Five Minutes in the Past
CDate() Function Convert to Date
Calculating Age "Good Enough"
Calculating Age in Whole Years
Display Total Time Worked in hh:nn
Display as 8:30, 8.5, and "08h 30m"
Timesheet Spanning Midnight
Original Worklog used Date and Time
What if you have only Time in your log?
Showing 24:00 instead of 00:00
Format two digits

04. Review (6:09)

 


 
Keywords: Comprehensive Function Guide, Date Time Functions, microsoft access tutorial, microsoft access tutorial, microsoft access training, access 2007, access 2010, access 2013, date, time, now, format, timestamp, aged accounts receivable, iif
 
 

Student Interaction: Microsoft Access Expert 27

Richard on 1/2/2015:  Microsoft Access Expert 27Access Expert 27 is Part 3 of our Comprehensive Guide to Access Functions. Today's class is part 1 of our focus on Date/Time Functions. You will learn some additional tricks with the Date, Time, Now, and Format functions. We will build an Aged Accounts Receivable, and we will learn more about date/time math operations. Topics include: - Date/Time Functions Part 1 of 2 - More with Now, Date, Time Functions - Query Data in a Variety of Date Ranges - Date & Time Mathematics - Two Digit Year "2030" Problem - List of Upcoming Birthdays - Aged Accounts Receivable Report - Display Times as 8:30, 8.5, 8h 30m - Timesheets Spanning Midnight - Calculating Someone's Approximate Age Click here for more information on Access Expert Level 27, 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 26. The next class in the series is Access Expert 28.
Alex Hedley on 1/3/2015: At about 11mins Rich mentions a Custom Function for adding st, nd, rd and th to a Date.

This was first mentioned in the Forum.

Function:

Function myOrdinal (D as Integer) as String
Dim Suffix as String
Select Case D
Case 1, 21, 31: Suffix = "st"
Case 2, 22: Suffix = "nd"
Case 3, 23: Suffix = "rd"
Case Else: Suffix = "th"
End Select
myOrdinal = D & Suffix
End Function


I've added it to my Row Number Tip too.

Alex Hedley on 1/3/2015: 7:20
To Do: "Reserved Words" Glossary Item/Blog Post

Update: I've added a Glossary Item with these listed.

Michelle C on 1/4/2015: Hi, Richard.

I was going to purchase Access X24 - X27 today, but the list of courses only goes up to X24. Just saying. :)

Reply from Richard Rost:

Which list are you looking at? The order form goes all the way up to 27.

Robert Stockey on 1/25/2015: Is there a handbook for this lesson

Reply from Alex Hedley:

I've just finished it yesterday so Rich should have it online soon, I think he's just gone away so might be a few days before it's available.

Wayne Markel on 5/3/2015: How do I download the PCResale Customer Database?

Reply from Alex Hedley:

All the sample Student Databases are available here. Just have your course password handy to unlock the zip.

John Newton on 10/4/2015: I vote less time on formating, if you spent the 50+ hrs watching so far; you likely saw a bunch already. We know you hate alternating colors. (I hate too, but we heard you the first 100 times).
I do have a format type question however; when you cut and paste from detail to headed (for example), sometimes Access wants to put the pasted stuff in the upper left corner. Why? Can I aviod that and have it copy at my curser?

Reply from Alex Hedley:

It's just the default location, I don't think it can be changed.

John N on 10/8/2015: Thanks. As I read my orginal post, I realized ii likely came across a bitsnotty. Sorry, Note to self, don't post stuff after midnight.

Reply from Alex Hedley:

No problem at all.
It's hard for Rich to find a balance as to how much to show, the hardcore Access users who have seen every video he's done, like us don't need it but the occasional user who may dip in and out probably does.
Normally the Seminars skip these types of things for that reason.

 

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

 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
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
Gift CDs
Contact
 
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP