Average Payment in Days

[Karen]

I inserted this statement and it worked great for the customers that
have no payment history, but for the customers that have a history and
an open invoice, it still used today's date in the mix and came back
with screwy math. For instance, a customer who actually has an
average payment of 20 days and has an open invoice 12 days old was
calculated at an average of 102 days.... I'm fuzzy, but not that
fuzzy! How do I tell Access to only use today if there is no payment
date as of yet?
Re: Average Payment in Days
From: Brendan Reynolds
Date Posted: 2/11/2004 10:33:00 AM

It's not really possible to give a specific answer in 'easy English'
alone,
because Access doesn't understand English - it understands VBA and
SQL. So
here's the answer in both English *and* SQL! :-)

Use the NZ() function to substitute the current date if the payment
date is
NULL.

SELECT NZ([PaymentDate], Date()) AS TheDate FROM SomeTable

In query design view, that would look something like this (in the
'Field'
row)

TheDate: NZ([PaymentDate], Date())

'TheDate' is just a label for the calculated column - you can call it
whatever you like. If you don't give it a label ('alias', in SQL
terminology) Access calls it something like 'Expr1', which isn't
exactly
memorable or enlightening! :-)

--
Brendan Reynolds

"Karen" <kr****@portlite.com> wrote in message
news:50*************************@posting.google.co m...

I need to run a query that will give me the average number of days it
takes a cutomer to pay. I have done this in a select query by
subtracting payment date from invoice date, then averaging, however, I
would like it to use current date if there is no payment history for
the customer or if the customer has an open invoice. How do I
calculate this?

I am not a programmer, so speak in easy english please. :)

[Stewart Allen]

I would use 2 queries here. The first would work out the payment time taken
for every individual invoice and use Brendon's logic for invoices not paid
and the second would then average the first query.

**Query1 (or what name you call it)**
In the query grid select the following fields from your Invoice table
CustomerID, InvoiceDate, TheDate: NZ([PaymentDate], Date()), DaysToPay:
DateDiff("d", [InvoiceDate], [TheDate])

**Query2**
Create a new query and use Query 1 as the record source and select
CustomerID and DaysToPay into the design grid. Select View>Totals from the
menu and change the Group By under the DaysToPay to Avg.

Stewart
"Karen" <kr****@portlite.com> wrote in message
news:50**************************@posting.google.c om...

I inserted this statement and it worked great for the customers that
have no payment history, but for the customers that have a history and
an open invoice, it still used today's date in the mix and came back
with screwy math. For instance, a customer who actually has an
average payment of 20 days and has an open invoice 12 days old was
calculated at an average of 102 days.... I'm fuzzy, but not that
fuzzy! How do I tell Access to only use today if there is no payment
date as of yet?
Re: Average Payment in Days
From: Brendan Reynolds
Date Posted: 2/11/2004 10:33:00 AM

It's not really possible to give a specific answer in 'easy English'
alone,
because Access doesn't understand English - it understands VBA and
SQL. So
here's the answer in both English *and* SQL! :-)

Use the NZ() function to substitute the current date if the payment
date is
NULL.

SELECT NZ([PaymentDate], Date()) AS TheDate FROM SomeTable

In query design view, that would look something like this (in the
'Field'
row)

TheDate: NZ([PaymentDate], Date())

'TheDate' is just a label for the calculated column - you can call it
whatever you like. If you don't give it a label ('alias', in SQL
terminology) Access calls it something like 'Expr1', which isn't
exactly
memorable or enlightening! :-)

--
Brendan Reynolds

"Karen" <kr****@portlite.com> wrote in message
news:50*************************@posting.google.co m...

I need to run a query that will give me the average number of days it
takes a cutomer to pay. I have done this in a select query by
subtracting payment date from invoice date, then averaging, however, I
would like it to use current date if there is no payment history for
the customer or if the customer has an open invoice. How do I
calculate this?

I am not a programmer, so speak in easy english please. :)

[Brendan Reynolds]

I expect one of the other responses in this thread may solve your problem.
If not, post a reply here and I'll take another look at it.

--
Brendan Reynolds
"Karen" <kr****@portlite.com> wrote in message
news:50**************************@posting.google.c om...

I inserted this statement and it worked great for the customers that
have no payment history, but for the customers that have a history and
an open invoice, it still used today's date in the mix and came back
with screwy math. For instance, a customer who actually has an
average payment of 20 days and has an open invoice 12 days old was
calculated at an average of 102 days.... I'm fuzzy, but not that
fuzzy! How do I tell Access to only use today if there is no payment
date as of yet?
Re: Average Payment in Days
From: Brendan Reynolds
Date Posted: 2/11/2004 10:33:00 AM

It's not really possible to give a specific answer in 'easy English'
alone,
because Access doesn't understand English - it understands VBA and
SQL. So
here's the answer in both English *and* SQL! :-)

Use the NZ() function to substitute the current date if the payment
date is
NULL.

SELECT NZ([PaymentDate], Date()) AS TheDate FROM SomeTable

In query design view, that would look something like this (in the
'Field'
row)

TheDate: NZ([PaymentDate], Date())

'TheDate' is just a label for the calculated column - you can call it
whatever you like. If you don't give it a label ('alias', in SQL
terminology) Access calls it something like 'Expr1', which isn't
exactly
memorable or enlightening! :-)

--
Brendan Reynolds

"Karen" <kr****@portlite.com> wrote in message
news:50*************************@posting.google.co m...

I need to run a query that will give me the average number of days it
takes a cutomer to pay. I have done this in a select query by
subtracting payment date from invoice date, then averaging, however, I
would like it to use current date if there is no payment history for
the customer or if the customer has an open invoice. How do I
calculate this?

I am not a programmer, so speak in easy english please. :)