Aging Invoice Report

[LiquorGuy via AccessMonster.com]

Hello,

I am trying to create and unpaid aging invoice report. I have used to forum
to obtain several hints and have been able to create a basic aging inventory
report that looks as follows:

SELECT tblOrders.AcctNumber, tblOrders.ItemNumber, tblOrders.InvoiceDate, IIf
(DateDiff("d",InvoiceDate,Date())<=30,InvoiceAmount,0) AS [0-30], IIf
(DateDiff("d",InvoiceDate,Date())>30 And DateDiff("d",InvoiceDate,Date())<=60,
InvoiceAmount,0) AS [31-60], IIf(DateDiff("d",InvoiceDate,Date())>60 And
DateDiff("d",InvoiceDate,Date())<=90,InvoiceAmount,0) AS [61-90], IIf
(DateDiff("d",InvoiceDate,Date())>90,InvoiceAmount,0) AS [91+]
FROM (tblCustomers INNER JOIN Payments ON tblCustomers.AcctNumber=Payments.
AcctNumber) INNER JOIN tblOrders ON tblCustomers.AcctNumber=tblOrders.
AcctNumber;

This just displays a query that shows invoices that are within a specified
date range i realize. My questions are as follows:

1) what is the significance of the "d" in the code above, and how can I
eliminate it without getting the invalid arguments error. As it stands right
now, I am prompted for 2 parameter values, the customer name & customer
address.. I can click through those but I would like to eliminate the error
all together

2) How can I modify this code so that it shows unpaid balances as opposed to
all invoices?

Thank you!

 

[John Spencer]

"d" is the argument that tells DateDiff to calculate the number of Days and
not the number of months (m), Hours(h), Years(yyyy), etc.


SELECT tblOrders.AcctNumber
, tblOrders.ItemNumber
, tblOrders.InvoiceDate
, IIf (DateDiff("d",InvoiceDate,Date())<=30,InvoiceAmount,0) AS [0-30]
, IIf(DateDiff("d",InvoiceDate,Date())>30 And
DateDiff("d",InvoiceDate,Date())<=60,InvoiceAmount,0) AS [31-60]
, IIf(DateDiff("d",InvoiceDate,Date())>60 And
DateDiff("d",InvoiceDate,Date())<=90,InvoiceAmount,0) AS [61-90]
, IIf(DateDiff("d",InvoiceDate,Date())>90,InvoiceAmount,0) AS [91+]
FROM (tblCustomers INNER JOIN Payments
ON tblCustomers.AcctNumber=Payments.AcctNumber)
INNER JOIN tblOrders
ON tblCustomers.AcctNumber=tblOrders.AcctNumber;

As a guess, you had the fields customername and customeraddress in you query
at one time and filtered or sorted by them. Check the properties of the
query. If that doesn't fix it, check the properties of the form or report
that is using the query.

How do you know what has an unpaid balance? Do you have a payment field in
this table or do you have a payments table or ????

 

[LiquorGuy via AccessMonster.com]

Hi John,

Thanks for your help. There are no suspicious items in the query. It is a
standalone query, (i.e not in a form) so I am not sorting by anything. And I
still have the error that pops up.

And yes, I do have a payments table that has PaymentID (primary key) ,
AcctNumber, PaymentAmount, PaymentDate, PaymentMethodID (which links to
another table that has check, cash, or credit card). I hope this clears up
some things

"d" is the argument that tells DateDiff to calculate the number of Days and
not the number of months (m), Hours(h), Years(yyyy), etc.

SELECT tblOrders.AcctNumber
, tblOrders.ItemNumber
, tblOrders.InvoiceDate
, IIf (DateDiff("d",InvoiceDate,Date())<=30,InvoiceAmount,0) AS [0-30]
, IIf(DateDiff("d",InvoiceDate,Date())>30 And
DateDiff("d",InvoiceDate,Date())<=60,InvoiceAmount,0) AS [31-60]
, IIf(DateDiff("d",InvoiceDate,Date())>60 And
DateDiff("d",InvoiceDate,Date())<=90,InvoiceAmount,0) AS [61-90]
, IIf(DateDiff("d",InvoiceDate,Date())>90,InvoiceAmount,0) AS [91+]
FROM (tblCustomers INNER JOIN Payments
ON tblCustomers.AcctNumber=Payments.AcctNumber)
INNER JOIN tblOrders
ON tblCustomers.AcctNumber=tblOrders.AcctNumber;

As a guess, you had the fields customername and customeraddress in you query
at one time and filtered or sorted by them. Check the properties of the
query. If that doesn't fix it, check the properties of the form or report
that is using the query.

How do you know what has an unpaid balance? Do you have a payment field in
this table or do you have a payments table or ????

Hello,

[quoted text clipped - 33 lines]

Thank you!

 

[John Spencer]

Try copying the SQL Text into a new query and running that.

Do you still get the parameter requests?

Hi John,

Thanks for your help. There are no suspicious items in the query. It is a
standalone query, (i.e not in a form) so I am not sorting by anything. And I
still have the error that pops up.

And yes, I do have a payments table that has PaymentID (primary key) ,
AcctNumber, PaymentAmount, PaymentDate, PaymentMethodID (which links to
another table that has check, cash, or credit card). I hope this clears up
some things

"d" is the argument that tells DateDiff to calculate the number of Days and
not the number of months (m), Hours(h), Years(yyyy), etc.

SELECT tblOrders.AcctNumber
, tblOrders.ItemNumber
, tblOrders.InvoiceDate
, IIf (DateDiff("d",InvoiceDate,Date())<=30,InvoiceAmount,0) AS [0-30]
, IIf(DateDiff("d",InvoiceDate,Date())>30 And
DateDiff("d",InvoiceDate,Date())<=60,InvoiceAmount,0) AS [31-60]
, IIf(DateDiff("d",InvoiceDate,Date())>60 And
DateDiff("d",InvoiceDate,Date())<=90,InvoiceAmount,0) AS [61-90]
, IIf(DateDiff("d",InvoiceDate,Date())>90,InvoiceAmount,0) AS [91+]
FROM (tblCustomers INNER JOIN Payments
ON tblCustomers.AcctNumber=Payments.AcctNumber)
INNER JOIN tblOrders
ON tblCustomers.AcctNumber=tblOrders.AcctNumber;

As a guess, you had the fields customername and customeraddress in you query
at one time and filtered or sorted by them. Check the properties of the
query. If that doesn't fix it, check the properties of the form or report
that is using the query.

How do you know what has an unpaid balance? Do you have a payment field in
this table or do you have a payments table or ????

Hello,

[quoted text clipped - 33 lines]

Thank you!

 

[John Spencer]

You could add a where clause to the end

WHERE InvoiceAmount <
(SELECT Sum(PaymentAmount)
FROM Payments as P
WHERE P.AcctNumber = TblOrders.AcctNumber)
OR Not Exists
(Select *
FROM Payments as P
WHERE P.AcctNumber = TblOrders.AcctNumber)

Or you could try using a subquery in the following manner

SELECT tblOrders.AcctNumber
, tblOrders.ItemNumber
, tblOrders.InvoiceDate
, IIf (DateDiff("d",InvoiceDate,Date())<=30,InvoiceAmount,0) AS [0-30]
, IIf(DateDiff("d",InvoiceDate,Date())>30 And
DateDiff("d",InvoiceDate,Date())<=60,InvoiceAmount,0) AS [31-60]
, IIf(DateDiff("d",InvoiceDate,Date())>60 And
DateDiff("d",InvoiceDate,Date())<=90,InvoiceAmount,0) AS [61-90]
, IIf(DateDiff("d",InvoiceDate,Date())>90,InvoiceAmount,0) AS [91+]
, TotalPaid
FROM (tblCustomers LEFT JOIN
(SELECT Sum(PaymentAmount) as TotalPaid, AcctNumber
FROM Payments
GROUP BY AcctNumber) as P
ON tblCustomers.AcctNumber=P.AcctNumber)
INNER JOIN tblOrders

Hi John,

Thanks for your help. There are no suspicious items in the query. It is a
standalone query, (i.e not in a form) so I am not sorting by anything. And I
still have the error that pops up.

And yes, I do have a payments table that has PaymentID (primary key) ,
AcctNumber, PaymentAmount, PaymentDate, PaymentMethodID (which links to
another table that has check, cash, or credit card). I hope this clears up
some things

"d" is the argument that tells DateDiff to calculate the number of Days and
not the number of months (m), Hours(h), Years(yyyy), etc.

SELECT tblOrders.AcctNumber
, tblOrders.ItemNumber
, tblOrders.InvoiceDate
, IIf (DateDiff("d",InvoiceDate,Date())<=30,InvoiceAmount,0) AS [0-30]
, IIf(DateDiff("d",InvoiceDate,Date())>30 And
DateDiff("d",InvoiceDate,Date())<=60,InvoiceAmount,0) AS [31-60]
, IIf(DateDiff("d",InvoiceDate,Date())>60 And
DateDiff("d",InvoiceDate,Date())<=90,InvoiceAmount,0) AS [61-90]
, IIf(DateDiff("d",InvoiceDate,Date())>90,InvoiceAmount,0) AS [91+]
FROM (tblCustomers INNER JOIN Payments
ON tblCustomers.AcctNumber=Payments.AcctNumber)
INNER JOIN tblOrders
ON tblCustomers.AcctNumber=tblOrders.AcctNumber;

As a guess, you had the fields customername and customeraddress in you query
at one time and filtered or sorted by them. Check the properties of the
query. If that doesn't fix it, check the properties of the form or report
that is using the query.

How do you know what has an unpaid balance? Do you have a payment field in
this table or do you have a payments table or ????

Hello,

[quoted text clipped - 33 lines]

Thank you!

 

[LiquorGuy via AccessMonster.com]

Yes I still get the parameter requests. Could it be something related to my
table design because I have the tables linked?

Try copying the SQL Text into a new query and running that.

Do you still get the parameter requests?

Hi John,

[quoted text clipped - 40 lines]

 

[LiquorGuy via AccessMonster.com]

When I add the following code to the end of the SQL statement, the output
doesn't change. It just shows the invoice amount in a seperate column, but
the initial information is still present. FYI, each payment doesnt
necessarily apply to a corresponding invoice, it just applies to the account
as a whole

You could add a where clause to the end

WHERE InvoiceAmount <
(SELECT Sum(PaymentAmount)
FROM Payments as P
WHERE P.AcctNumber = TblOrders.AcctNumber)
OR Not Exists
(Select *
FROM Payments as P
WHERE P.AcctNumber = TblOrders.AcctNumber)

Or you could try using a subquery in the following manner

SELECT tblOrders.AcctNumber
, tblOrders.ItemNumber
, tblOrders.InvoiceDate
, IIf (DateDiff("d",InvoiceDate,Date())<=30,InvoiceAmount,0) AS [0-30]
, IIf(DateDiff("d",InvoiceDate,Date())>30 And
DateDiff("d",InvoiceDate,Date())<=60,InvoiceAmount,0) AS [31-60]
, IIf(DateDiff("d",InvoiceDate,Date())>60 And
DateDiff("d",InvoiceDate,Date())<=90,InvoiceAmount,0) AS [61-90]
, IIf(DateDiff("d",InvoiceDate,Date())>90,InvoiceAmount,0) AS [91+]
, TotalPaid
FROM (tblCustomers LEFT JOIN
(SELECT Sum(PaymentAmount) as TotalPaid, AcctNumber
FROM Payments
GROUP BY AcctNumber) as P
ON tblCustomers.AcctNumber=P.AcctNumber)
INNER JOIN tblOrders
ON tblCustomers.AcctNumber=tblOrders.AcctNumber
WHERE InvoiceAmount - Nz(TotalPaid,0) > 0

Hi John,

[quoted text clipped - 40 lines]

 

[John Spencer]

I'm stumped.

If you created a new blank query and pasted the SQL text into the SQL view
of the query, then I have no idea how you could be getting parameter
requests. Unless you are basing this query on another query which is
causing the parameter requests. It didn't seem to that you were using any
other query in what you posted.

Yes I still get the parameter requests. Could it be something related to
my
table design because I have the tables linked?

Try copying the SQL Text into a new query and running that.

Do you still get the parameter requests?

Hi John,

[quoted text clipped - 40 lines]

 

[John Spencer]

If you can't tie a payment to an invoice, then I'm not sure how you can
decide whether an invoice is paid or not. If you have invoices for $20,
$30, and $50 on one AcctNumber and have payments totaling $90 then all the
invoices would show up as Not Paid in full.

I could think of some SQL that would do a running sum of invoice amounts and
then compare that to the payments received and then use that as criteria for
selecting those invoices whose running sum is greater than the total amount
of payments received for the acctNumber.

However, that will probably have to be left up to you to do. I don't have
the time to invest in solving the problem at this time.

When I add the following code to the end of the SQL statement, the output
doesn't change. It just shows the invoice amount in a seperate column,
but
the initial information is still present. FYI, each payment doesnt
necessarily apply to a corresponding invoice, it just applies to the
account
as a whole

You could add a where clause to the end

WHERE InvoiceAmount <
(SELECT Sum(PaymentAmount)
FROM Payments as P
WHERE P.AcctNumber = TblOrders.AcctNumber)
OR Not Exists
(Select *
FROM Payments as P
WHERE P.AcctNumber = TblOrders.AcctNumber)

Or you could try using a subquery in the following manner

SELECT tblOrders.AcctNumber
, tblOrders.ItemNumber
, tblOrders.InvoiceDate
, IIf (DateDiff("d",InvoiceDate,Date())<=30,InvoiceAmount,0) AS [0-30]
, IIf(DateDiff("d",InvoiceDate,Date())>30 And
DateDiff("d",InvoiceDate,Date())<=60,InvoiceAmount,0) AS [31-60]
, IIf(DateDiff("d",InvoiceDate,Date())>60 And
DateDiff("d",InvoiceDate,Date())<=90,InvoiceAmount,0) AS [61-90]
, IIf(DateDiff("d",InvoiceDate,Date())>90,InvoiceAmount,0) AS [91+]
, TotalPaid
FROM (tblCustomers LEFT JOIN
(SELECT Sum(PaymentAmount) as TotalPaid, AcctNumber
FROM Payments
GROUP BY AcctNumber) as P
ON tblCustomers.AcctNumber=P.AcctNumber)
INNER JOIN tblOrders
ON tblCustomers.AcctNumber=tblOrders.AcctNumber
WHERE InvoiceAmount - Nz(TotalPaid,0) > 0

Hi John,

[quoted text clipped - 40 lines]