Access 2007-2019 Access 2000-2003 Access Seminars TechHelp Support Tips & Tricks Access Forum Course Index Topic Glossary Insider Circle

 Home   News   Courses   Tips   Templates   Forums   Help   Order   Contact   Logon More... What's New? Tips & Tricks Access Index Access Tips Excel Index Excel Tips Waiting List

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

 - 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,

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.

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 KeysInsert DateInsert Time Insert Date from Previous RecordDate Time Functions Date(), Time(), Now() FunctionsDisplay Current Date in Unbound Form FieldUse as Default Value in a Table Field Use as Default Value in a Form FieldFormat Function ParametersShow Date as "Monday, December 15, 2014"Two Digit Year Cutoff - 2030 ProblemAutomatically Timestamp New RecordsRecords From TodayRecords in the PastRecords in the FutureRecords on a Specific DateRecords NOT on a Specific DateRecords Between Two DatesRecords NOT Between Two DatesRecords Outside of a Date RangeRecords 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

 You may want to read these articles from the 599CD News:
 8/4/2020 Access Restore Deleted AutoNumber 8/1/2020 Access Custom Sort Order 7/30/2020 Access SharePoint Seminar 7/29/2020 Microsoft Access Developer 16 7/29/2020 Is There a Free Version of Access? 7/28/2020 Access Disallow Editing Data 7/25/2020 Access NZ Function NULL to Zero 7/24/2020 Access Link Parent Child Same Table 7/23/2020 Access OnCurrent Event TechHelp 7/20/2020 Access Troubleshooting Checklist

 Learn  Access - index Excel - index Word - index Windows - index PowerPoint - index Photoshop - index Visual Basic - index ASP - index Seminars More... Customers  Account Login Online Theater Downloads Lost Password Free Upgrades Insider Circle Student Databases Change Email Info  Latest News New Releases User Forums Topic Glossary Tips & Tricks Articles Search The Site Waiting List Production Schedule Collapse Menus Help  Live Chat Customer Support WalkThru Tutorials Troubleshooting FAQs TechHelp Consulting Services About  Background Testimonials Jobs Affiliate Program Richard Rost Free Lessons Mailing List Order  Video Tutorials Handbooks MYOLP Memberships Learning Connection Idiot's Guide to Excel Volume Discounts Payment Info Shipping Terms of Sale Contact  Live Chat General Info Support Policy Contact Form Email Richard Mailing Address Phone Number Fax Number Course Survey