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