Consider a query that has columns for DateDue, AmountDue, and DaysOverdue. DaysOverdue is a calculated field and can be used in expressions for other calculated fields. The formula for DaysOverdue is: DateDiff("d", [DateDue], Date()). DateDiff finds the difference between two dates and returns the result in the specified time interval, such as days, months, or years. Date() returns the current date and is like the TODAY function in Excel.
To see if DaysOverdue is between 1 and 30, we’ll use the IIf (Immediate If) function, which can be used anywhere you can use an expression and works much like the IF function in Excel. Three parameters, or parts, must be specified—the condition to test, the value if true, and the value if false—using the syntax IIf(condition to test, value if true, value if false). Each part can be an expression.
Even though IIf returns only one value, both the true and false value expressions are evaluated. It’s important to consider the possibility that one of the expressions might result in an error. If that’s the case, the formula won’t work even if the other expression would be the one applied. For example, if one formula might result in a division by zero, you can’t ignore it because you think it won’t be used and that the other part will apply. Nesting IIf statements or other functions, such as Nz (null to zero, or specified optional second parameter) and IsNull (a logical function that indicates whether an expression contains data), can be used to help avoid errors.
With the query open in Design view, click in the Field cell of an empty column and open the Expression Builder to create the expression shown in Figure 1: Due30: IIf([DaysOverdue] Between 1 And 30,[AmountDue],Null).
The name of the calculated field, Due30, is before the colon. If a name isn’t assigned, Access will use an arbitrary placeholder such as Expr1. When choosing a name, it’s best to start with a letter, avoid using spaces or special characters except underscores, and make it sensible and related to the field’s use.
The function tests the value in DaysOverdue to see if it falls within 1 and 30, inclusively. If it does, then the function returns the value in the amount due field (AmountDue). If it doesn’t, then no value (Null) is returned. If you want a zero when there’s no value, either use a 0 in its place within the formula or set the Format property of the column to show Null as zero. (While in the column, press Alt+Enter to open the property sheet.) An advantage of returning Null rather than 0 is that when values are counted, those with no value are excluded.
In the same way, Due60, Due90, and Due120 can be calculated by changing the “Between 1 And 30” range in the formula to 31 and 60, 61 and 90, and 91 and 120, respectively. For amounts older than 120 days, use Over120: IIf([DaysOverdue]>120,[AmountDue],Null). To calculate the receivables that aren’t overdue, use DueCurrent: IIf([DaysOverdue]<1,[AmountDue],Null).
If the amount is due today, Date() - DateDue will be zero. If the due date is in the future, then the result will be negative. Rather than displaying a negative number, you can specify a custom number format code on the Property Sheet for the column. Like Excel, custom number format codes have different “zones,” as Bill Jelen has called them in Strategic Finance’s Excel column. Others might call them “parts,” “sections,” “arguments,” or “parameters.” The first zone defines how positive values will look (if only one zone is specified, it will be used for all numbers). Additional zones can be specified. They are delimited with a semicolon. The second zone is for negative values, the third is for zero, and the fourth for Null.
Named formats that follow Windows region settings also can be used, such as Fixed (at least one digit), Standard (include the thousand separator), Percent (multiply by 100, display percent sign), Scientific (standard scientific notation), Currency (include the thousand separator, display currency symbol), and General Number (display as entered).
For our purposes, enter a format code of #,##0.00;"Not Overdue" in the property sheet for the DueCurrent column. This will display positive numbers using a comma to separate the thousands and including two decimal places, while negative numbers will result in the words “Not Overdue” being displayed. Save the query with the name, “qMoneyDue_with_Aging.”
To add the Totals Row below the records, open the query in Datasheet view. On the Home tab, click the Totals icon in the Records group. This toggles the Totals row for aggregating values on or off. Turn on the Totals row.
In each of the columns that show an amount, click in the Total cell. From the dropdown, which displays the list of aggregate functions, choose Sum (see Figure 2). If you choose Count, only the records that aren’t Null will be counted.
While a query can only show one row of totals, a report can show multiple rows, like if you want to show both Sum and Count as well as other statistics, such as Maximum and Minimum.
Download this month’s database: sf-1706-Aging_Database.
June 2017
