Accounts Payable 4
By Richard Rost
11 months ago
Build an Accounts Payable Database in Access, Part 4 In this Microsoft Access tutorial, I will show you how to build a query to categorize your accounts payable items based on their due dates using the IIf function. You will learn how to identify amounts that are past due, due in seven days, thirty days, and over thirty days, as well as how to set up calculated fields for each group. This is part 4. MembersThere is no extended cut, but here is the file download: Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
PrerequisitesLinksRecommended CoursesUp Next
Keywords TechHelp Access, accounts payable series, conditional formatting, payables form, past due report, aging report, IIf function, query criteria is null, calculated fields, date calculations, due in seven days, due in thirty days, aging accounts payable, field aliasing, grouping by due date
Intro In this video, we continue our Microsoft Access accounts payable series by building a query to organize and categorize unpaid payables. You'll learn how to use criteria to filter out paid items, create calculated fields with the IIf function, alias those fields, and sort your payables into categories like past due, due in seven or thirty days, and over thirty days. We will also discuss how to avoid duplicate results in your date-based queries, best practices for naming fields, and the basics of aging accounts payable. This is part 4.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost.
Today is part four of my accounts payable series. If you haven't watched parts one through three yet, go watch those first and then come on back.
The database is looking pretty good so far. We have our payables form here. We have our conditional format again. Now it's time to make a report so you can quickly see who is past due and for how much, how much is due in seven days and 30 days, and how much is not due for 30 days yet, so we can plan what's coming in.
Let's add a couple more items here. This guy is due in 30, this guy is overdue. I'm going to add another one for Jean-Luc. Let's say for 500, and we'll put this in here as due in the future, so 8/15. We'll add one more person in here. Let's pick Firefly, Inc. for 250, and let's make this one due in a couple of days, so the 17th.
Looks good. Now, before we can build the report, we have to build a query, and the query will let us set up each item and then whether it's past due, due in seven, and so on. To do that we're going to use our friend, the IIf function. It's the IIf function, not the if function. I say that because a lot of times people send me emails or post in the forums and everything's correct except they spell the function wrong: "iff." It's not; it's IIf. That's why I say it--it's stupid and it's silly, but hopefully it will help you remember it: immediate if, not iff.
Okay, anyways, let's create a query. Bring in the built-in tables. What do we want to see in here? Let's bring in the PayID. The PaidDate needs to be null, so criteria down here is going to be "Is Null." I don't want any paid items showing up in this list.
Now, next column is going to be, is this item past due? If it is, put the amount here, and if not, put a zero here. To make that decision, I'm going to use the IIf function.
Zoom in so you can see (Shift+F2).
We're going to call this field "PastDue." That's called aliasing--I'm giving this calculated query field a name, calling it PastDue. It's going to be IIf the DueDate is less than today's date, then put the AmountDue in here, otherwise put a zero.
Click OK, and let's check it and see it first. Let's save this guy. Save this as "MyPayablesQ." Normally I try to keep these things singular, but in this case "payables" is fine.
Run it, and there we go. It looks like only the payable for customer 1 is past due. Let's verify that. Customer 1 is past due, and that is correct.
Let's add another column now. We know here are the past due people. Let's add another one to see who is due in the next, let's say, seven days. Very similar to what we did before.
So Shift+F2, let's call this one "DueInSeven:". If the DueDate is less than today's date plus seven, then put the AmountDue here, otherwise put a zero.
Click OK. Run it, and there you go. Now, we don't want to see this guy showing up twice, so we've got to modify it just slightly. Right-click, Design View.
This one is less than today's date, so I need the other one to say, okay, if it's greater than or equal to today's date, then go back over to this guy. You've got to be less than date plus seven and the DueDate is greater than or equal to today's date.
Normally what I like to do with dates is reverse this. I like to put the "greater than" in the front, and that's just my style. That should work fine either way, but I like to have the smaller date to the left. That's just me.
Click OK.
Save it and run it, and there we go. Now number six, Payee 6, the item is only showing up once. Let's double check it. Payee 6, oh, wait, that's all on this--that is okay. PayeeID 6, and that's the BillID, so Payee 6, I think this one is this one here. Yes, and that's due in six days.
Perfect. I'll do a couple more.
So here's "DueInSeven." I'm just going to copy and paste this guy, and then Shift+F2. "DueInThirty" is going to be greater than or equal to date plus seven and less than date plus 30.
Click OK, then run it. Looks good.
Finally we want "OverThirty" days. Copy this one, paste it in here, then Shift+F2. Call this one "OverThirty." I do my best, by the way, to not start field names with numbers. I only use letters and numbers, and don't start your field with a number because sometimes you can't.
So this is going to be if DueDate is greater than or equal to date plus 30, and we can get rid of this unless you want 30-60-90, then keep going because many of these you want.
Click OK. This is called aged accounts payable, by the way. Aging means you see when stuff is due. I do the same thing with aged accounts receivable in my class.
I think I've got a TechHelp video for that too.
Looks good. Let's add one more so we can see. Save changes, yes. Let's add one more person in here. Let's do Must Do Enterprise, Inc. for $1,701, and the due date will be 6/20. Check our query and looks good.
Now let's take this and put this into a report. We're going to tackle that in the next class.
Now, today is Thursday, the 12th of June, 2025. Tomorrow is Friday, which is Quick Queries Friday, so we're going to cover this on Monday the 16th.
If you're watching this in the future, go watch it right now. One of the benefits of being a member is that you can watch it right now, because I'm going to keep recording it.
That is going to do it for today, folks, at your TechHelp video. Hope you learned something. Live long and prosper, my friends. I'll see you Monday for part five and tomorrow for Quick Queries Friday.
TOPICS:
Creating a query to show unpaid payables
Using criteria to filter paid and unpaid items
Building calculated fields with the IIf function
Aliasing calculated fields in queries
Classifying payables as past due
Calculating payables due in seven days
Calculating payables due in thirty days
Calculating payables due in over thirty days
Avoiding duplicate payables in date-based queries
Best practices for naming query fields
Understanding aging of accounts payable
COMMERCIAL:
In today's video, we're continuing with part four of the accounts payable series. We'll learn how to create a query to break down your payables into categories like past due, due in seven days, due in thirty days, and not due for thirty days or more. I'll show you how to use the IIf function to set up calculations for each category, making it easy to see which bills need attention and when. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.Quiz Q1. What is the purpose of creating a report in this accounts payable tutorial?
A. To enter new vendor invoices
B. To see who is past due, due soon, and to plan for future payables
C. To delete old payment records
D. To reconcile bank statements
Q2. Before building the accounts payable report, which step does the tutorial emphasize first?
A. Designing forms for data entry
B. Creating a query to organize payables by due date
C. Exporting data to Excel
D. Setting up user permissions
Q3. What function is used in the query to determine whether an item is past due or falls into another category?
A. SUM
B. IIf
C. Switch
D. IF
Q4. Which spelling of the function is emphasized as correct in the tutorial?
A. If
B. iff
C. IIf
D. Iif
Q5. What criteria is applied to PaidDate in the query to exclude certain records?
A. Is Not Null
B. Is Null
C. Greater than today
D. Equals today's date
Q6. In the query, what does the alias "PastDue" represent?
A. Whether the item is an expense
B. The total amount paid
C. Amount due if overdue, or zero otherwise
D. The date payment was made
Q7. How does the query categorize items due within the next seven days?
A. By subtracting today's date from the due date
B. By checking if the due date is less than today's date plus seven, and due date is greater than or equal to today
C. By comparing the amount due to a fixed amount
D. By checking if the PaidDate is in the past
Q8. What is the correct way to structure field names according to the tutorial?
A. Field names can start with numbers
B. Only use special characters in field names
C. Use letters and numbers, do not start field names with numbers
D. Use spaces in all field names
Q9. What is the process of aging accounts payable?
A. Calculating vendor discounts
B. Categorizing payables by how soon they are due or overdue
C. Renaming all the tables
D. Linking databases together
Q10. What is the suggested name for saving the accounts payable query?
A. PayablesTable
B. PayListQ
C. MyPayablesQ
D. PaymentsUpcoming
Q11. If you wanted to add more categories to the report (such as 30-60-90 days), what should you do?
A. Delete old queries
B. Add more calculated fields to the query using similar logic
C. Increase the amount due for every record
D. Change all due dates to today's date
Answers: 1-B; 2-B; 3-B; 4-C; 5-B; 6-C; 7-B; 8-C; 9-B; 10-C; 11-B
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 TechHelp tutorial from Access Learning Zone continues our accounts payable series with part four. If you have not yet watched the previous three parts, I recommend starting there to get up to speed before continuing with today's lesson.
Our database is coming along nicely. So far, we have designed a payables form and set up conditional formatting. The next step is to create a report to provide a clear overview of which payables are past due along with amounts due soon, due in seven days, due in thirty days, and those not yet due within thirty days. This will help with planning and keeping track of upcoming financial obligations.
To illustrate the process, I added a few more example records. For example, one item is due in thirty days, another is already overdue, I created a future due item for Jean-Luc for five hundred dollars, assigning it a due date of August 15, and then I added an entry for Firefly, Inc. for two hundred fifty dollars, due in a few days.
Before we can create the actual report, we need to build a query. This query will serve as the source for our report and allow us to categorize each payable as past due, due in seven days, due in thirty days, or not yet due. To accomplish this kind of categorization, we will rely on the IIf function in Access. Remember, it is spelled "I-I-f," not "iff" or "if." I always emphasize this because it is a very common source of confusion for many students and database users.
Let's start by creating the query, bringing in the necessary tables. We want to display items such as the PayID, and importantly, we need to exclude records that have already been paid. So, we set the PaidDate field's criteria to "Is Null" in order to filter out completed payments.
Next, we need to determine which items are past due. For this, I use a calculated field that evaluates whether the DueDate is before the current date using the IIf function. If the condition is true, it displays the AmountDue; otherwise, it returns zero. I refer to this field as "PastDue," which is an alias. Aliasing lets us assign a name to calculated fields in our queries. After writing the expression, I always run the query to make sure it works as expected. At this stage, only the customers whose payments are overdue will show up in the PastDue column with the amount displayed, and the rest will show zero.
After verifying past due amounts, we move on to create additional columns to see which payments are due in the next seven days. Again, we use the IIf function to check if the DueDate falls within seven days from today. Initially, if you just check for less than today plus seven, you might see duplicate information where overdue payments also appear in this seven-day period. To avoid that, I add a condition to check that the DueDate is greater than or equal to today, making sure we do not count overdue items twice.
As a personal preference, when working with date ranges, I usually list the smaller value first (using "greater than" on the left). This does not affect the logic but helps me stay organized and makes the conditions easier to read.
Once the logic is set, we run and verify the query to ensure each payment appears in the correct time frame.
The process is then repeated for payables due within thirty days. Here, I copy the seven-day column's logic, adjust the range to cover from seven days out to thirty days, and give this column the name "DueInThirty." After confirming it works, I repeat the process for payments that are due in more than thirty days by calling that column "OverThirty." I always recommend starting field names with letters, not numbers, to avoid any potential issues.
This method of identifying and categorizing payables by their due date is part of what is known as aged accounts payable. It is a standard approach for managing both accounts payable and accounts receivable, helping you keep track of what is due when.
To wrap up today's lesson, I added another test record for Must Do Enterprise, Inc., entered an amount, and assigned a due date of June 20th. After checking the query, everything fell into place.
In the next class, we will take this query and use it to generate a report. At the time of this recording, that next lesson is scheduled for Monday the 16th of June, 2025. If you are a member, you may already have access to the next part. For everyone else, it will be available soon.
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 Creating a query to show unpaid payables
Using criteria to filter paid and unpaid items
Building calculated fields with the IIf function
Aliasing calculated fields in queries
Classifying payables as past due
Calculating payables due in seven days
Calculating payables due in thirty days
Calculating payables due in over thirty days
Avoiding duplicate payables in date-based queries
Best practices for naming query fields
Understanding aging of accounts payable |
