30 Days Late on Invoice Question !

  • Thread starter Thread starter Dave Elliott
  • Start date Start date
D

Dave Elliott

In my Database I have a Orders table and a Customers table and a query that
combines them both called Order Information.
In the query grid I have the code below and it works fine showing on the
form and on the report if the customer is late and the Per-Centage due.
My question is this, How do I track the graduated payments since I only have
one bound control for the result and that is Order Date.
If the customer is late with there Payment Terms on the customer form set at
Due on Receipt and they are say 30 days late, how do I add this per centage
automatically to the Invoice. Also say if they are 60 days late and the per
centage increases ?
I can put in on the Report to add the per centage amount to the gross total,
but what if they are 60 days late, what then ?
I dont know how to make it just not add the 30 day amount or the 60 days
amount and not both ! 90 days as well.

Customers and Orders Tables. Order Date.

What can I do to fix this ?

Thanks,

Dave

Days Past Due: DateDiff("d",Now(),[Order Date])
0-30: IIf([Days Past Due]<-31,CCur([Order Amount]*0.02),0)
0-60: IIf([Days Past Due]<-61,CCur([Order Amount]*0.03),0)
0-90: IIf([Days Past Due]<-91,CCur([Order Amount]*0.04),0)
 
Start with the latest date period and the go down from there. In other
words, first test to see if it's greater than 60 and apply the calculation
if yes, otherwise test to see if it's greater than 30 and apply the
calculation if yes, otherwise test if it's greater than 0. This way, you'll
only apply the most high number range.

Days Past Due: DateDiff("d",Now(),[Order Date])

IIf([Days Past Due]<-61,CCur([Order Amount]*0.04), IIf([Days Past
Due]<-31,CCur([Order Amount]*0.03), IIf([Days Past Due]<0,CCur([Order
Amount]*0.02),0)))
 
Thanks, will give that a try.
dave

Ken Snell said:
Start with the latest date period and the go down from there. In other
words, first test to see if it's greater than 60 and apply the calculation
if yes, otherwise test to see if it's greater than 30 and apply the
calculation if yes, otherwise test if it's greater than 0. This way, you'll
only apply the most high number range.

Days Past Due: DateDiff("d",Now(),[Order Date])

IIf([Days Past Due]<-61,CCur([Order Amount]*0.04), IIf([Days Past
Due]<-31,CCur([Order Amount]*0.03), IIf([Days Past Due]<0,CCur([Order
Amount]*0.02),0)))


--
Ken Snell
<MS ACCESS MVP>

Dave Elliott said:
In my Database I have a Orders table and a Customers table and a query that
combines them both called Order Information.
In the query grid I have the code below and it works fine showing on the
form and on the report if the customer is late and the Per-Centage due.
My question is this, How do I track the graduated payments since I only have
one bound control for the result and that is Order Date.
If the customer is late with there Payment Terms on the customer form
Click to expand...
set
at
Due on Receipt and they are say 30 days late, how do I add this per centage
automatically to the Invoice. Also say if they are 60 days late and the per
centage increases ?
I can put in on the Report to add the per centage amount to the gross total,
but what if they are 60 days late, what then ?
I dont know how to make it just not add the 30 day amount or the 60 days
amount and not both ! 90 days as well.

Customers and Orders Tables. Order Date.

What can I do to fix this ?

Thanks,

Dave

Days Past Due: DateDiff("d",Now(),[Order Date])
0-30: IIf([Days Past Due]<-31,CCur([Order Amount]*0.02),0)
0-60: IIf([Days Past Due]<-61,CCur([Order Amount]*0.03),0)
0-90: IIf([Days Past Due]<-91,CCur([Order Amount]*0.04),0)
Click to expand...
Click to expand...
 
Ok, that works, but now how do I make the total add just the greatest amount
to it ?
For example I have a report based on the query and it will show 30 days
amount,60 days amount, 90 days amount. if any at all.
How do I make the total (Without the &'s) add in which ever is greater ?

Thanks,
Dave


Ken Snell said:
Start with the latest date period and the go down from there. In other
words, first test to see if it's greater than 60 and apply the calculation
if yes, otherwise test to see if it's greater than 30 and apply the
calculation if yes, otherwise test if it's greater than 0. This way, you'll
only apply the most high number range.

Days Past Due: DateDiff("d",Now(),[Order Date])

IIf([Days Past Due]<-61,CCur([Order Amount]*0.04), IIf([Days Past
Due]<-31,CCur([Order Amount]*0.03), IIf([Days Past Due]<0,CCur([Order
Amount]*0.02),0)))


--
Ken Snell
<MS ACCESS MVP>

Dave Elliott said:
In my Database I have a Orders table and a Customers table and a query that
combines them both called Order Information.
In the query grid I have the code below and it works fine showing on the
form and on the report if the customer is late and the Per-Centage due.
My question is this, How do I track the graduated payments since I only have
one bound control for the result and that is Order Date.
If the customer is late with there Payment Terms on the customer form
Click to expand...
set
at
Due on Receipt and they are say 30 days late, how do I add this per centage
automatically to the Invoice. Also say if they are 60 days late and the per
centage increases ?
I can put in on the Report to add the per centage amount to the gross total,
but what if they are 60 days late, what then ?
I dont know how to make it just not add the 30 day amount or the 60 days
amount and not both ! 90 days as well.

Customers and Orders Tables. Order Date.

What can I do to fix this ?

Thanks,

Dave

Days Past Due: DateDiff("d",Now(),[Order Date])
0-30: IIf([Days Past Due]<-31,CCur([Order Amount]*0.02),0)
0-60: IIf([Days Past Due]<-61,CCur([Order Amount]*0.03),0)
0-90: IIf([Days Past Due]<-91,CCur([Order Amount]*0.04),0)
Click to expand...
Click to expand...
 
AdjustedTotal = [Order Amount] + IIf([Days Past Due]<-61,CCur([Order
Amount]*0.04), IIf([Days Past Due]<-31,CCur([Order Amount]*0.03), IIf([Days
Past Due]<0,CCur([Order Amount]*0.02),0)))

or, more simply,

AdjustedTotal = IIf([Days Past Due]<-61,CCur([Order Amount]*1.04), IIf([Days
Past Due]<-31,CCur([Order Amount]*1.03), IIf([Days Past Due]<0,CCur([Order
Amount]*1.02),0)))



--
Ken Snell
<MS ACCESS MVP>

Dave Elliott said:
Ok, that works, but now how do I make the total add just the greatest amount
to it ?
For example I have a report based on the query and it will show 30 days
amount,60 days amount, 90 days amount. if any at all.
How do I make the total (Without the &'s) add in which ever is greater ?

Thanks,
Dave


Ken Snell said:
Start with the latest date period and the go down from there. In other
words, first test to see if it's greater than 60 and apply the calculation
if yes, otherwise test to see if it's greater than 30 and apply the
calculation if yes, otherwise test if it's greater than 0. This way, you'll
only apply the most high number range.

Days Past Due: DateDiff("d",Now(),[Order Date])

IIf([Days Past Due]<-61,CCur([Order Amount]*0.04), IIf([Days Past
Due]<-31,CCur([Order Amount]*0.03), IIf([Days Past Due]<0,CCur([Order
Amount]*0.02),0)))


--
Ken Snell
<MS ACCESS MVP>

Dave Elliott said:
In my Database I have a Orders table and a Customers table and a query that
combines them both called Order Information.
In the query grid I have the code below and it works fine showing on the
form and on the report if the customer is late and the Per-Centage due.
My question is this, How do I track the graduated payments since I
Click to expand...
only
have
one bound control for the result and that is Order Date.
If the customer is late with there Payment Terms on the customer form
Click to expand...
set
at
Due on Receipt and they are say 30 days late, how do I add this per centage
automatically to the Invoice. Also say if they are 60 days late and
Click to expand...
the
per
centage increases ?
I can put in on the Report to add the per centage amount to the gross total,
but what if they are 60 days late, what then ?
I dont know how to make it just not add the 30 day amount or the 60 days
amount and not both ! 90 days as well.

Customers and Orders Tables. Order Date.

What can I do to fix this ?

Thanks,

Dave

Days Past Due: DateDiff("d",Now(),[Order Date])
0-30: IIf([Days Past Due]<-31,CCur([Order Amount]*0.02),0)
0-60: IIf([Days Past Due]<-61,CCur([Order Amount]*0.03),0)
0-90: IIf([Days Past Due]<-91,CCur([Order Amount]*0.04),0)
Click to expand...
Click to expand...
Click to expand...
 
Ken: The report will work if I just use one customer at a time and only then
if the customer is 30 day's late.
It wont add any late fees to any customers if you show all Invoices.
What am I missing ?

Thanks,

Dave
 
Dave -

I'm not sure how you're trying to use this on the report. Is this expression
meant to be the control source for a textbox?

I did note an error in my posted example. Here is corrected version:

AdjustedTotal = [Order Amount] + IIf([Days Past Due]<-61,CCur([Order
Amount]*0.04), IIf([Days Past Due]<-31,CCur([Order Amount]*0.03), IIf([Days
Past Due]<0,CCur([Order Amount]*0.02),0)))

or, more simply,

AdjustedTotal = IIf([Days Past Due]<-61,CCur([Order Amount]*1.04), IIf([Days
Past Due]<-31,CCur([Order Amount]*1.03), IIf([Days Past Due]<0,CCur([Order
Amount]*1.02),[Order Amount])))

I need to have some more info about your report's setup and recordsource so
that I might gain an idea about why you're seeing this result.

--
Ken Snell
<MS ACCESS MVP>

Dave Elliott said:
Ken: The report will work if I just use one customer at a time and only then
if the customer is 30 day's late.
It wont add any late fees to any customers if you show all Invoices.
What am I missing ?

Thanks,

Dave

Dave Elliott said:
In my Database I have a Orders table and a Customers table and a query that
combines them both called Order Information.
In the query grid I have the code below and it works fine showing on the
form and on the report if the customer is late and the Per-Centage due.
My question is this, How do I track the graduated payments since I only have
one bound control for the result and that is Order Date.
If the customer is late with there Payment Terms on the customer form
Click to expand...
set
at
Due on Receipt and they are say 30 days late, how do I add this per centage
automatically to the Invoice. Also say if they are 60 days late and the per
centage increases ?
I can put in on the Report to add the per centage amount to the gross total,
but what if they are 60 days late, what then ?
I dont know how to make it just not add the 30 day amount or the 60 days
amount and not both ! 90 days as well.

Customers and Orders Tables. Order Date.

What can I do to fix this ?

Thanks,

Dave

Days Past Due: DateDiff("d",Now(),[Order Date])
0-30: IIf([Days Past Due]<-31,CCur([Order Amount]*0.02),0)
0-60: IIf([Days Past Due]<-61,CCur([Order Amount]*0.03),0)
0-90: IIf([Days Past Due]<-91,CCur([Order Amount]*0.04),0)
Click to expand...
Click to expand...
 
One thing that I just noted....don't use Now(), use Date() in your DateDiff
function. Now contains a time component, and only at midnight is the time
portion zero. Date always has the time portion as zero. I'm assuming that
your Order Date field has no time component to it.

Thus, even when the customer is not late, your DateDiff function will still
return a nonzero value.

--
Ken Snell
<MS ACCESS MVP>

Ken Snell said:
Dave -

I'm not sure how you're trying to use this on the report. Is this expression
meant to be the control source for a textbox?

I did note an error in my posted example. Here is corrected version:

AdjustedTotal = [Order Amount] + IIf([Days Past Due]<-61,CCur([Order
Amount]*0.04), IIf([Days Past Due]<-31,CCur([Order Amount]*0.03), IIf([Days
Past Due]<0,CCur([Order Amount]*0.02),0)))

or, more simply,

AdjustedTotal = IIf([Days Past Due]<-61,CCur([Order Amount]*1.04), IIf([Days
Past Due]<-31,CCur([Order Amount]*1.03), IIf([Days Past Due]<0,CCur([Order
Amount]*1.02),[Order Amount])))

I need to have some more info about your report's setup and recordsource so
that I might gain an idea about why you're seeing this result.

--
Ken Snell
<MS ACCESS MVP>

Dave Elliott said:
Ken: The report will work if I just use one customer at a time and only then
if the customer is 30 day's late.
It wont add any late fees to any customers if you show all Invoices.
What am I missing ?

Thanks,

Dave

Dave Elliott said:
In my Database I have a Orders table and a Customers table and a query that
combines them both called Order Information.
In the query grid I have the code below and it works fine showing on the
form and on the report if the customer is late and the Per-Centage due.
My question is this, How do I track the graduated payments since I
Click to expand...
only
have
one bound control for the result and that is Order Date.
If the customer is late with there Payment Terms on the customer form
Click to expand...
set
at
Due on Receipt and they are say 30 days late, how do I add this per centage
automatically to the Invoice. Also say if they are 60 days late and
Click to expand...
the
per
centage increases ?
I can put in on the Report to add the per centage amount to the gross total,
but what if they are 60 days late, what then ?
I dont know how to make it just not add the 30 day amount or the 60 days
amount and not both ! 90 days as well.

Customers and Orders Tables. Order Date.

What can I do to fix this ?

Thanks,

Dave

Days Past Due: DateDiff("d",Now(),[Order Date])
0-30: IIf([Days Past Due]<-31,CCur([Order Amount]*0.02),0)
0-60: IIf([Days Past Due]<-61,CCur([Order Amount]*0.03),0)
0-90: IIf([Days Past Due]<-91,CCur([Order Amount]*0.04),0)
Click to expand...
Click to expand...
Click to expand...
 
Back
Top