Debtors Ledger

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
 
N

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





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
 
M

Marshall Barton

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


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

Newbie

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Crosstab repor and total 3
Crosstab . . .again 3
count 1

Top