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