Introduction

Welcome! Date Math & Accounts Receivable

S M L XL FS | Slo Reg Fast 2x | Bookmark | Autoplay: ON

Welcome to Microsoft Access Expert Level 27. In this course we will focus on working with dates and times in Access. We will cover the Date, Time, and Now functions, discuss date mathematics, and build an accounts receivable aging calculation. We will also talk about different ways to display hour and minute data, and the fundamentals of handling date/time values. Before beginning, it is recommended that you complete the Beginner series and Expert Levels 1 through 26, as we build on those skills throughout this Expert Level 27 class.

Navigation

Prev: X27
Next: Lesson 01

Keywords

Access Expert, date functions, time functions, now function, date math, accounts receivable aging, date/time formatting, age calculation, fractional days, displaying hours and minutes, sample database, Northwind Traders, upgrading Access, event programmin

Subscribe to Introduction
Get notifications when this page is updated

More Information

Transcript

Welcome to Microsoft Access Expert Level 27, brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. Today's class is Part 3 of my Comprehensive Function Guide for Microsoft Access.

Part 1, which was Access Expert 25, covered string and logical functions. Part 2 covered math and type conversion functions. Part 3, which is today's class, Expert 27, will cover the Date, Time, and Now functions, date mathematics, and will build an age to count receivable.

Originally, I had planned for this to be one big class, but it got way too long. It is well over three hours. So I split this up into two classes, 27 and 28. Expert 28 will cover many more different date/time functions, as well as a bunch of new examples.

Today's class covers all the fundamentals and sets up how to work with dates and times. The next class, Expert 28, will have a bunch of new functions and some additional examples to work with.

If you want to learn how to work with dates and times properly in Access, I recommend both classes starting with 27. Before taking this class, it is strongly recommended that you have finished my Beginner series and Expert Levels 1 through 26.

This class was recorded using Access 2013. Everything covered today should work with 2007 and 2010. I am pretty sure that most of the functions covered today also work with 2003, but I cannot guarantee it. If you are using 2003 or older, you really should upgrade to 2013.

My courses are broken up into Beginner, Expert, Advanced, and Developer level classes. Beginner level classes are for novices. You should understand all the topics covered in them by the time you get to the Expert level classes, which you are in now. When you finish all the Expert level classes, the Advanced classes will cover event programming and macros, and the Developer classes will cover Visual Basic for Applications.

Each group of classes is broken down into multiple levels: Level 1, 2, 3, and so on.

In addition to my normal Access classes, I also have seminars designed to teach specific topics. Some of my seminars include building web-based databases, creating forms and reports that look like calendars, securing your database, working with images and attachments, writing work orders and running a service business, tracking accounts payable, learning the SQL programming language, creating loan amortization schedules, and lots more. You can find details on all of these seminars and more on the website at accesslearningzone.com.

If you have questions about the topics covered in today's lessons, please feel free to post them in my student forums. If you are watching this course in the online theater on my website, you should see the student forum for each lesson appear in a small window next to the class video. Here you will see all of the questions that other students have asked, as well as my responses to them and comments that other students have made. I encourage you to read through these questions and answers as you start each lesson and feel free to join in the discussion.

If you are not watching these lessons on my website, you can still visit the student forums later by visiting accesslearningzone.com/forums.

To get the most out of this course, I recommend you sit back, relax, and watch each lesson completely through once without trying to do anything on your computer. Then, replay the lesson from the beginning and follow along with my examples. Actually create the same database that I make in the video, step by step. Do not try to apply what you are learning right now to other projects until you have mastered the sample database from class.

If you get stuck or do not understand something, watch the video again from the beginning or tell me what is wrong in the student forum and I will do my best to help you.

Most importantly, keep an open mind. Access may seem intimidating at first, but once you get the hang of it, you will see that it is really easy to use.

I strongly encourage you to build the database that I build in today's class by following along with the videos. However, if you would like to download a sample copy of my finished database file, you can find it on my website at accesslearningzone.com/databases. Sometimes, if you get stuck, the easiest way to learn is to tear apart someone else's database.

One of the ways that I taught myself Access years ago was by tearing apart the Northwind Traders database that comes up in Microsoft Access. You will find there is a sample database for each of my courses on my website.

Now let us take a few minutes and go over exactly what we are going to cover in today's class. In lesson one, we are going to begin taking a close look at the Date, Time, and Now functions. In lesson two, we are continuing on with the Date, Time, and Now functions. In this lesson, we are going to build an accounts receivable with aging. In lesson three, we are continuing with date/time values. We are going to see how to use hours and minutes as a fraction of a day and we will learn some different ways to display that data.

Thank you.

Intro

Quiz

Q1. What primary topics are covered in Access Expert Level 27?
A. Date, Time, and Now functions, date mathematics, and building an accounts receivable with aging
B. String and logical functions only
C. Programming with Visual Basic for Applications
D. Report formatting and image attachment techniques

Q2. Which versions of Microsoft Access does the material in Expert 27 most likely support?
A. Only 2013 and newer
B. 2007, 2010, and 2013 (most likely 2003, but not guaranteed)
C. Only 2003 and older
D. Only Access Online

Q3. What is recommended before starting Access Expert Level 27?
A. Working through Beginner and Expert Levels 1 through 26
B. Having advanced knowledge of SQL
C. Completing only the Beginner series
D. Reading Access documentation

Q4. What should a student do if they have questions about the topics in the class?
A. Post them in the student forums
B. Wait for Richard to email them back
C. Contact Microsoft support
D. Skip to the next lesson

Q5. What is the teaching method suggested for mastering the material in this course?
A. Take detailed notes instead of watching
B. Watch the lesson, replay it, and follow along by building the same database step by step
C. Only read the manual
D. Memorize the entire transcript first

Q6. What topics will be covered in Access Expert Level 28, according to the video?
A. More date/time functions and additional examples
B. String and logical functions
C. Advanced SQL programming
D. Macros and event programming

Q7. If a student gets stuck while following along, what should they do?
A. Watch the video again from the beginning or ask for help in the student forum
B. Give up and skip to the next class
C. Contact a third-party forum
D. Skip the video and read only the answers

Q8. What is an advantage of downloading the sample database file from the website?
A. You can tear apart and learn from someone else's finished database
B. It is impossible to make mistakes
C. It provides reference documentation only
D. It will automatically build your own project

Q9. What is the major difference between Beginner and Expert level classes as described?
A. Beginner covers basics; Expert assumes you have mastered those basics for more advanced topics
B. Beginner uses only Access Online
C. Expert is entirely focused on macros and VBA
D. Beginner is longer than Expert

Q10. Which function types are the focus of the first lesson in Expert Level 27?
A. Date, Time, and Now functions
B. Image and attachment functions
C. Security and permissions functions
D. Event handling functions

Answers: 1-A; 2-B; 3-A; 4-A; 5-B; 6-A; 7-A; 8-A; 9-A; 10-A

DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.

Summary

Today's video from Access Learning Zone is part 3 of the Comprehensive Function Guide for Microsoft Access. I am your instructor, Richard Rost, and we're now at Access Expert Level 27. In the previous lessons, Expert 25 covered string and logical functions, and the next class went over math and type conversion functions. This session is focused on the Date, Time, and Now functions, along with date arithmetic, and we'll also set up an age calculation for accounts receivable.

I originally intended to fit all of this material into one class, but it became too lengthy, so I split the content across two levels: Expert 27 and 28. Expert 28 will dive further into more date and time functions with a larger set of examples. Today's class will give you all the foundational knowledge you'll need to handle dates and times in Access, and then you can move on to the next class for more advanced topics.

If you want to work with dates and times correctly in Access, you should start with this course, Expert 27, and then continue with Expert 28. Please make sure you've completed the entire Beginner series, as well as Expert Levels 1 through 26, before you continue with this material.

This class was recorded using Microsoft Access 2013, and everything here works with Access 2007 and 2010 as well. While many of the functions do work in 2003, I cannot guarantee complete functionality if you are still using that version. If you are, I really recommend upgrading to Access 2013.

Let me explain a little about how my courses are structured. They fall into four categories: Beginner, Expert, Advanced, and Developer. The Beginner courses are set up for those with little or no experience, and by the time you reach the Expert level, you should be comfortable with all the concepts from the Beginner classes. Advanced courses go deeper, especially into event programming and macros, while the Developer levels are focused on Visual Basic for Applications. Within each skill group, the lessons progress from Level 1 upward.

In addition to these classes, I offer seminars on specialized topics such as building web-based databases, creating calendar-style forms and reports, database security, handling images and attachments, managing work orders and service businesses, tracking accounts payable, learning SQL, and making loan amortization schedules. Details about all these seminars are on my website at accesslearningzone.com.

If you have questions as you go through any of these lessons, feel free to use the student forums. If you are watching on my website, the forum will appear next to the lesson video, showing questions and answers from other students as well as my own replies. It's a good idea to read through these forums and join the discussions. If you are watching elsewhere, you can always visit the forums later at accesslearningzone.com/forums.

For the best results, my advice is to watch each lesson all the way through first, without trying to follow along in Access right away. Then, go back and work through the examples step-by-step, recreating the database as I build it in the video. This approach really helps reinforce what you are learning. Wait to apply these techniques to your own projects until you've mastered them with the examples in class.

If you run into issues, try watching the video again, or describe your problem in the student forum and I'll do my best to assist you.

Keep an open mind as you learn this material. Working with Access can seem overwhelming at first, but once you understand the basics, you'll see how straightforward it can be.

I always recommend following along with building the sample database in each class. If you would rather, or if you get stuck, you can download a copy of my finished database files from my website at accesslearningzone.com/databases. Sometimes the best way to learn is to look at someone else's completed database and figure out how it works.

Over the years, I taught myself Access by pulling apart the Northwind Traders database, which is a classic good example. On my website, you can find a sample database for each course I teach.

Let me quickly go over what we'll be working on in today's class. In the first lesson, we will explore the Date, Time, and Now functions in detail. The second lesson continues with these functions, and in this part, we will create an accounts receivable tracking system with aging. In the third lesson, we will learn more about handling date and time values, including how to work with hours and minutes as fractions of a day, and how to display them in different ways.

You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.

Topic List

Date function in Microsoft Access
Time function in Microsoft Access
Now function in Microsoft Access
Basic date mathematics in Access
Building accounts receivable aging
Working with date/time values
Handling hours and minutes as fractional days
Formatting and displaying date/time values

Article

Working with Dates and Times in Microsoft Access: A Practical Guide

If you want to manage information involving dates and times in your Microsoft Access database, understanding how to use Access's built-in date and time functions is essential. In this guide, I will walk you through the basic concepts, demonstrate essential functions such as Date, Time, and Now, and show you how to perform date arithmetic like calculating the age of an account receivable. By the end, you should feel comfortable working with dates and times in your own databases.

Microsoft Access offers several functions to help you handle dates and times effectively. The three fundamental ones are Date(), Time(), and Now(). Date() returns today's date based on your system clock, returning just the date portion with the time set to midnight. Time() returns the current system time, showing the hours, minutes, and seconds, with the date portion set to a base value (for practical purposes, you only get the time). Now() returns both the current date and the current time in a single field.

To see these functions in action, you can open the Immediate Window in the VBA editor and type:
? Date
? Time
? Now

These commands will display the current date, time, and date with time, respectively. You can also use these functions in queries, forms, and reports. For example, you could set the Default Value property of a field to Date() or Now() to automatically populate records with the current date or date and time upon entry.

One very common thing to do with dates is to calculate the difference between two dates. Whether you are tracking when an invoice was sent and when it was paid, or you want to show the age of something in days, you can subtract two dates. In Access, dates are stored internally as numbers, where the integer portion represents the date and the decimal portion represents the time. This means you can simply subtract one date from another, and you will get the number of days between them. For example, if you enter =Date() - #1/1/2024# in the Immediate Window, you will see how many days have passed since January 1, 2024.

Building on this, suppose you want to know how old a receivable is. Imagine you have a table of invoices with a field called InvoiceDate. To calculate the age in days, you can create a calculated field in a query with the expression: Age: Date() - [InvoiceDate]. This will show you the age of each invoice in days.

It is common practice to track receivables by placing them into aging categories such as current, 30 days, 60 days, 90 days, and so on. You can do this using expressions in queries or reports. For example, you could use:
Current: IIf([Age] <= 30, [Amount], 0)
ThirtyDays: IIf([Age] > 30 And [Age] <= 60, [Amount], 0)
SixtyDays: IIf([Age] > 60 And [Age] <= 90, [Amount], 0)
NinetyDays: IIf([Age] > 90, [Amount], 0)

Replace [Amount] with the actual amount of the receivable. You can then sum up these fields to display how much money falls into each aging category.

When dealing with times, remember that in Access, times are represented as fractions of a day. For example, 0.5 represents 12:00 PM (noon), because it is half of a 24 hour day. The Time() function returns a value like .625 if it is 3:00 PM (since 3 PM is 15 hours, and 15/24 = 0.625). You can format these fields in your forms and reports using the Format property. If you want to display only the time portion, use a format such as "hh:nn:ss" or "Short Time" in the Format property.

If you want to calculate with times, for example, to add minutes or hours, you need to convert units. Since one day is 24 hours, one hour is 1/24, one minute is 1/(24*60), and one second is 1/(24*60*60). For instance, to add two hours to a time, you could use:
=[StartTime] + (2/24)

This would give you a value exactly two hours after the time in [StartTime].

If you need to extract the hours, minutes, or seconds from a time value, you can use the Hour(), Minute(), and Second() functions. For example:
=Hour([StartTime])
=Minute([StartTime])
=Second([StartTime])

This is useful if you want to separate out the components of a time for reporting or calculations.

For more complex calculations, like determining the difference between two dates in months or years, Access provides the DateDiff() function. For example, to find out how many days have passed between two dates:
=DateDiff("d", [StartDate], [EndDate])

To get the number of months:
=DateDiff("m", [StartDate], [EndDate])

If you want to calculate age in years, you can use:
=DateDiff("yyyy", [BirthDate], Date()) - IIf(Format([BirthDate], "mmdd") > Format(Date(), "mmdd"), 1, 0)

This logic subtracts an additional year if the person's birthday for the current year has not occurred yet.

Let me give you a practical example of building an Accounts Receivable Aging report. Suppose you have a table of invoices, each with an InvoiceDate and an Amount. You can create a query adding a calculated field for age, such as Age: Date() - [InvoiceDate]. Then, create calculated fields for each aging category as shown earlier using IIf functions. You can then sum these fields in your report to show totals for each aging bracket.

If you find yourself struggling at any point, you can always look at sample databases such as the Northwind Traders sample, or download sample files where you can study queries and forms that already work. Often, the easiest way to learn is by taking apart a working example.

Getting comfortable with date and time functions in Access is vital for managing records that depend on when things happen. Once you understand how Access stores dates and times and how to use its functions and expressions, you can easily perform calculations like aging receivables, measuring durations, and breaking down time intervals for your reports. Try creating a sample database and practice these calculations, and you will quickly see how powerful and flexible Microsoft Access can be.

Primary Topics

Date functions, Time functions, Now function, date math, accounts receivable aging, handling date/time values, fraction of a day, displaying date/time

Secondary Topics

function compatibility across Access versions, best practices for learning Access, seminars overview