Debtors Ledger

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

Newbie

Hi,

I have the following query that works ok but I would like to be able to show
invoices that are older than 1 year if they are still outstanding

For eg
Report will have 14 columns

1 for each month
1 for Total O/S, and
1 for Invoices older than 1 year

At the moment the query just reports on invoices for the past year

How can I amend this to include the really late payers

FYI Here is what I have so far

PARAMETERS [forms]![frmSalesDates]![txtDateEnd] DateTime;
TRANSFORM Sum(Round([InvoiceBal1],2)) AS Expr2
SELECT ArInvoice.Customer, ArCustomer.Name, ArCustomer.Branch,
Round(Abs(Sum(([InvoiceDate]>=DateAdd("yyyy",-1,forms!frmSalesDates!txtDateE
nd) And [InvoiceDate]<=forms!frmSalesDates!txtDateEnd)*[InvoiceBal1])),2) AS
[Total O/S]
FROM ArCustomer INNER JOIN ArInvoice ON ArCustomer.Customer =
ArInvoice.Customer
GROUP BY ArInvoice.Customer, ArCustomer.Name, ArCustomer.Branch
PIVOT "Mth" & DateDiff("m",[InvoiceDate],forms!frmSalesDates!txtDateEnd) In
("Mth0","Mth1","Mth2","Mth3","Mth4","Mth5","Mth6","Mth7","Mth8","Mth9","Mth1
0","Mth11");

Thanks
 
Ignore the last post

I have the following:

PARAMETERS [forms]![frmSalesDates]![txtDateEnd] DateTime;
TRANSFORM Sum(Round([InvoiceBal1],2)) AS Expr2
SELECT ArInvoice.Customer, ArCustomer.Name, ArCustomer.Branch,
Round(Abs(Sum(([InvoiceDate]>=DateAdd("yyyy",-1,forms!frmSalesDates!txtDateE
nd) And [InvoiceDate]<=forms!frmSalesDates!txtDateEnd)*[InvoiceBal1])),2) AS
[Total O/S]
FROM ArCustomer INNER JOIN ArInvoice ON ArCustomer.Customer =
ArInvoice.Customer
GROUP BY ArInvoice.Customer, ArCustomer.Name, ArCustomer.Branch
PIVOT "Mth" &
IIf(DateDiff("d",[InvoiceDate],[forms]![frmSalesDates]![txtDateEnd])>90,"90"
,(DateDiff("d",[InvoiceDate],[forms]![frmSalesDates]![txtDateEnd])\30)*30)
In ("Mth0","Mth30","Mth60","Mth90")

this gives me a ledger split into 0-30, 30-60, 69-90 and 90+ based on
Invoice Date

Is it possible so that the column it appears in is based on the month end
date for the month in which the invoice is dated

eg. for a report run with a date of 30/11/03 any invoices that were raised
in November would appear in the column Mth0
any invoices raised in October would appear in the column Mth30 etc





Newbie said:
Hi,

I have the following query that works ok but I would like to be able to show
invoices that are older than 1 year if they are still outstanding

For eg
Report will have 14 columns

1 for each month
1 for Total O/S, and
1 for Invoices older than 1 year

At the moment the query just reports on invoices for the past year

How can I amend this to include the really late payers

FYI Here is what I have so far

PARAMETERS [forms]![frmSalesDates]![txtDateEnd] DateTime;
TRANSFORM Sum(Round([InvoiceBal1],2)) AS Expr2
SELECT ArInvoice.Customer, ArCustomer.Name, ArCustomer.Branch,
Round(Abs(Sum(([InvoiceDate]>=DateAdd("yyyy",-1,forms!frmSalesDates!txtDateE
nd) And [InvoiceDate]<=forms!frmSalesDates!txtDateEnd)*[InvoiceBal1])),2) AS
[Total O/S]
FROM ArCustomer INNER JOIN ArInvoice ON ArCustomer.Customer =
ArInvoice.Customer
GROUP BY ArInvoice.Customer, ArCustomer.Name, ArCustomer.Branch
PIVOT "Mth" & DateDiff("m",[InvoiceDate],forms!frmSalesDates!txtDateEnd) In("Mth0","Mth1","Mth2","Mth3","Mth4","Mth5","Mth6","Mth7","Mth8","Mth9","Mth1
0","Mth11");

Thanks
Click to expand...
 
Newbie said:
I have the following:

PARAMETERS [forms]![frmSalesDates]![txtDateEnd] DateTime;
TRANSFORM Sum(Round([InvoiceBal1],2)) AS Expr2
SELECT ArInvoice.Customer, ArCustomer.Name, ArCustomer.Branch,
Round(Abs(Sum(([InvoiceDate]>=DateAdd("yyyy",-1,forms!frmSalesDates!txtDateE
nd) And [InvoiceDate]<=forms!frmSalesDates!txtDateEnd)*[InvoiceBal1])),2) AS
[Total O/S]
FROM ArCustomer INNER JOIN ArInvoice ON ArCustomer.Customer =
ArInvoice.Customer
GROUP BY ArInvoice.Customer, ArCustomer.Name, ArCustomer.Branch
PIVOT "Mth" &
IIf(DateDiff("d",[InvoiceDate],[forms]![frmSalesDates]![txtDateEnd])>90,"90"
,(DateDiff("d",[InvoiceDate],[forms]![frmSalesDates]![txtDateEnd])\30)*30)
In ("Mth0","Mth30","Mth60","Mth90")

this gives me a ledger split into 0-30, 30-60, 69-90 and 90+ based on
Invoice Date

Is it possible so that the column it appears in is based on the month end
date for the month in which the invoice is dated

eg. for a report run with a date of 30/11/03 any invoices that were raised
in November would appear in the column Mth0
any invoices raised in October would appear in the column Mth30 etc
Click to expand...


I think this will do whar you're looking for:

. . .
IIf(DateDiff("m",[InvoiceDate],[forms]![frmSalesDates]![txtDateEnd])>3,"90"
,(DateDiff("m",[InvoiceDate],[forms]![frmSalesDates]![txtDateEnd]))*30)
. . .

This way, it won't matter what day of the month DateEnd has.
This may make it a little more convenient to enter DateEnd
on the form since you could just enter the month and year.
e.g. 11/2003 or Nov 2003 (just don't try to use 11/02
instead of 11/2002).
 
Thanks - just what I was looking for


Marshall Barton said:
Newbie said:
I have the following:

PARAMETERS [forms]![frmSalesDates]![txtDateEnd] DateTime;
TRANSFORM Sum(Round([InvoiceBal1],2)) AS Expr2
SELECT ArInvoice.Customer, ArCustomer.Name, ArCustomer.Branch,
Round(Abs(Sum(([InvoiceDate]>=DateAdd("yyyy",-1,forms!frmSalesDates!txtDate
Click to expand...
E
nd) And [InvoiceDate]<=forms!frmSalesDates!txtDateEnd)*[InvoiceBal1])),2) AS
[Total O/S]
FROM ArCustomer INNER JOIN ArInvoice ON ArCustomer.Customer =
ArInvoice.Customer
GROUP BY ArInvoice.Customer, ArCustomer.Name, ArCustomer.Branch
PIVOT "Mth" &
IIf(DateDiff("d",[InvoiceDate],[forms]![frmSalesDates]![txtDateEnd])>90,"90
Click to expand...
"
,(DateDiff("d",[InvoiceDate],[forms]![frmSalesDates]![txtDateEnd])\30)*30)
In ("Mth0","Mth30","Mth60","Mth90")

this gives me a ledger split into 0-30, 30-60, 69-90 and 90+ based on
Invoice Date

Is it possible so that the column it appears in is based on the month end
date for the month in which the invoice is dated

eg. for a report run with a date of 30/11/03 any invoices that were raised
in November would appear in the column Mth0
any invoices raised in October would appear in the column Mth30 etc
Click to expand...


I think this will do whar you're looking for:

. . .
IIf(DateDiff("m",[InvoiceDate],[forms]![frmSalesDates]![txtDateEnd])>3,"90"
,(DateDiff("m",[InvoiceDate],[forms]![frmSalesDates]![txtDateEnd]))*30)
. . .

This way, it won't matter what day of the month DateEnd has.
This may make it a little more convenient to enter DateEnd
on the form since you could just enter the month and year.
e.g. 11/2003 or Nov 2003 (just don't try to use 11/02
instead of 11/2002).
Click to expand...
 
Back
Top