Crosstab aging report

  • Thread starter Thread starter Newbie
  • Start date Start date
N

Newbie

Office 2003

I want to create a report similar to an aged debt report. I want the
columns to be
0-14 days
15-30 days
31-60 days
61-90 days
90 days and over

I have the following (amended from a post I found by Duane) but am not sure
how to do the pivot section.
The DateDiff("d",[tDate],forms!frmDate.dtpFrom)/30)*30)) line that was part
of the original post didn't work as it didn't show the amounts in any column
when the result of the days was not in the Mth0 or Mth90 column

What do I need to do?
Thanks


PARAMETERS Forms!frmDate.dtpFrom DateTime;
TRANSFORM Sum(Debtors.AmountOS) AS TotalOS
SELECT Debtors.Acc, Debtors.AccName, Debtors.InvNo, Debtors.Note
FROM Debtors
GROUP BY Debtors.Acc, Debtors.AccName, Debtors.InvNo, Debtors.Note
PIVOT "Mth" &
IIf(DateDiff("d",[tDate],forms!frmDate.dtpFrom)>90,"90",iif(DateDiff("d",[tDate],forms!frmDate.dtpFrom)<15,
"14",
(DateDiff("d",[tDate],forms!frmDate.dtpFrom)/30)*30)) In ("Mth14","Mth30",
"Mth60","Mth90");
 
I would either create a public function in a module of business calculations
or a table of day ranges. I generally consider nesting more than 2 IIf()s a
mistake. The following is the function method. You can search google groups
for a table of ranges.

Public Function GetAging(datStart As Date, datEnd As Date) As String
Dim intDays As Integer
intDays = DateDiff("d", datStart, datEnd)
Select Case intDays
Case Is <= 14
GetAging = "0-14 Days"
Case Is <= 30
GetAging = "15-30 Days"
Case Is <= 60
GetAging = "31-60 Days"
Case Is <= 90
GetAging = "61-90 Days"
Case Else
GetAging = "90 Days and Over"
End Select
End Function

PARAMETERS Forms!frmDate.dtpFrom DateTime;
TRANSFORM Sum(Debtors.AmountOS) AS TotalOS
SELECT Debtors.Acc, Debtors.AccName, Debtors.InvNo, Debtors.Note
FROM Debtors
GROUP BY Debtors.Acc, Debtors.AccName, Debtors.InvNo, Debtors.Note
PIVOT GetAging([tDate],forms!frmDate.dtpFrom) In ("0-14 Days","15-30
Days",..etc..);
 
Brilliant - I had got it to work using the nested iifs but this function
looks so much more friendly

Thanks
A
Duane Hookom said:
I would either create a public function in a module of business
calculations or a table of day ranges. I generally consider nesting more
than 2 IIf()s a mistake. The following is the function method. You can
search google groups for a table of ranges.

Public Function GetAging(datStart As Date, datEnd As Date) As String
Dim intDays As Integer
intDays = DateDiff("d", datStart, datEnd)
Select Case intDays
Case Is <= 14
GetAging = "0-14 Days"
Case Is <= 30
GetAging = "15-30 Days"
Case Is <= 60
GetAging = "31-60 Days"
Case Is <= 90
GetAging = "61-90 Days"
Case Else
GetAging = "90 Days and Over"
End Select
End Function

PARAMETERS Forms!frmDate.dtpFrom DateTime;
TRANSFORM Sum(Debtors.AmountOS) AS TotalOS
SELECT Debtors.Acc, Debtors.AccName, Debtors.InvNo, Debtors.Note
FROM Debtors
GROUP BY Debtors.Acc, Debtors.AccName, Debtors.InvNo, Debtors.Note
PIVOT GetAging([tDate],forms!frmDate.dtpFrom) In ("0-14 Days","15-30
Days",..etc..);
--
Duane Hookom
MS Access MVP
--

Newbie said:
Office 2003

I want to create a report similar to an aged debt report. I want the
columns to be
0-14 days
15-30 days
31-60 days
61-90 days
90 days and over

I have the following (amended from a post I found by Duane) but am not
sure how to do the pivot section.
The DateDiff("d",[tDate],forms!frmDate.dtpFrom)/30)*30)) line that was
part of the original post didn't work as it didn't show the amounts in
any column when the result of the days was not in the Mth0 or Mth90
column

What do I need to do?
Thanks


PARAMETERS Forms!frmDate.dtpFrom DateTime;
TRANSFORM Sum(Debtors.AmountOS) AS TotalOS
SELECT Debtors.Acc, Debtors.AccName, Debtors.InvNo, Debtors.Note
FROM Debtors
GROUP BY Debtors.Acc, Debtors.AccName, Debtors.InvNo, Debtors.Note
PIVOT "Mth" &
IIf(DateDiff("d",[tDate],forms!frmDate.dtpFrom)>90,"90",iif(DateDiff("d",[tDate],forms!frmDate.dtpFrom)<15,
"14",
(DateDiff("d",[tDate],forms!frmDate.dtpFrom)/30)*30)) In
("Mth14","Mth30", "Mth60","Mth90");
Click to expand...
Click to expand...
 
WHEN your day ranges change, it is much easier to modify the function rather
than a horribly long nested IIf().

--
Duane Hookom
MS Access MVP
--

Newbie said:
Brilliant - I had got it to work using the nested iifs but this function
looks so much more friendly

Thanks
A
Duane Hookom said:
I would either create a public function in a module of business
calculations or a table of day ranges. I generally consider nesting more
than 2 IIf()s a mistake. The following is the function method. You can
search google groups for a table of ranges.

Public Function GetAging(datStart As Date, datEnd As Date) As String
Dim intDays As Integer
intDays = DateDiff("d", datStart, datEnd)
Select Case intDays
Case Is <= 14
GetAging = "0-14 Days"
Case Is <= 30
GetAging = "15-30 Days"
Case Is <= 60
GetAging = "31-60 Days"
Case Is <= 90
GetAging = "61-90 Days"
Case Else
GetAging = "90 Days and Over"
End Select
End Function

PARAMETERS Forms!frmDate.dtpFrom DateTime;
TRANSFORM Sum(Debtors.AmountOS) AS TotalOS
SELECT Debtors.Acc, Debtors.AccName, Debtors.InvNo, Debtors.Note
FROM Debtors
GROUP BY Debtors.Acc, Debtors.AccName, Debtors.InvNo, Debtors.Note
PIVOT GetAging([tDate],forms!frmDate.dtpFrom) In ("0-14 Days","15-30
Days",..etc..);
--
Duane Hookom
MS Access MVP
--

Newbie said:
Office 2003

I want to create a report similar to an aged debt report. I want the
columns to be
0-14 days
15-30 days
31-60 days
61-90 days
90 days and over

I have the following (amended from a post I found by Duane) but am not
sure how to do the pivot section.
The DateDiff("d",[tDate],forms!frmDate.dtpFrom)/30)*30)) line that was
part of the original post didn't work as it didn't show the amounts in
any column when the result of the days was not in the Mth0 or Mth90
column

What do I need to do?
Thanks


PARAMETERS Forms!frmDate.dtpFrom DateTime;
TRANSFORM Sum(Debtors.AmountOS) AS TotalOS
SELECT Debtors.Acc, Debtors.AccName, Debtors.InvNo, Debtors.Note
FROM Debtors
GROUP BY Debtors.Acc, Debtors.AccName, Debtors.InvNo, Debtors.Note
PIVOT "Mth" &
IIf(DateDiff("d",[tDate],forms!frmDate.dtpFrom)>90,"90",iif(DateDiff("d",[tDate],forms!frmDate.dtpFrom)<15,
"14",
(DateDiff("d",[tDate],forms!frmDate.dtpFrom)/30)*30)) In
("Mth14","Mth30", "Mth60","Mth90");
Click to expand...
Click to expand...
Click to expand...
 
Back
Top