Debtors Ledger

[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

 

[Newbie]

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

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

 

[Marshall Barton]

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

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).

 

[Newbie]

Thanks - just what I was looking for

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

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

"

,(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

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).