30,60,90 Days Due. 0 to30,31-59,60-89,90-120.

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

Dave Elliott

I realized that my code is not returning the result I want. If the
customer's bill is between 0 and 29 they pay the normal rate. If they are 30
to 59 days late, then they need to pay that %, which is 3%. The code below
does not do that. I think I need (2) pieces of code to accomplish this ?
zero to 29 days Original Invoice Amount. 30 to 59 Days, 3%, 60 to 89 days 4%
and 90 days and over 4%. Right now the code will give me , say if the
Customer is late 65 days, which puts them into the 3% bracket , it adds the
4% amount instead. Why? The customers bill is $1260.00 and the 60 days
per-centage is $37.80 at 3%, but the formula gives it 4%, which is $50.00
making the total come to $1310.40. . .
Thanks, Dave

=IIf([DaysPastDue]<=-91,CCur([Order Amount]*0.04),[Order
Amount]+IIf([DaysPastDue]<=-61,CCur([Order
Amount]*0.04),IIf([DaysPastDue]<=-31,CCur([Order
Amount]*0.03),IIf([DaysPastDue]<=0,CCur([Order Amount]*0.02),0))))
 
Dave,
It is using 4% at 65 days because that is what you tell it to do and what
you told us you want:

"...60 to 89 days 4%..."

"...IIf([DaysPastDue]<=-61,CCur([Order
Amount]*0.04),..."

Sean
 
Your getting your terminology mixed up. The dates you list are th enumber
of days from the invoice date, not number of days late. If you are looking
for something that is 65 days late, that would be 95 days from the invoice
date which puts you in the 4% range. Also, your percentages for 60-89 and
90 are the same. If so, just check for >60. Whay would you need to check
Click to expand...
for both in this case?

Kelvin

Dave Elliott said:
I realized that my code is not returning the result I want. If the
customer's bill is between 0 and 29 they pay the normal rate. If they are 30
to 59 days late, then they need to pay that %, which is 3%. The code below
does not do that. I think I need (2) pieces of code to accomplish this ?
zero to 29 days Original Invoice Amount. 30 to 59 Days, 3%, 60 to 89 days 4%
and 90 days and over 4%. Right now the code will give me , say if the
Customer is late 65 days, which puts them into the 3% bracket , it adds the
4% amount instead. Why? The customers bill is $1260.00 and the 60 days
per-centage is $37.80 at 3%, but the formula gives it 4%, which is $50.00
making the total come to $1310.40. . .
Thanks, Dave

=IIf([DaysPastDue]<=-91,CCur([Order Amount]*0.04),[Order
Amount]+IIf([DaysPastDue]<=-61,CCur([Order
Amount]*0.04),IIf([DaysPastDue]<=-31,CCur([Order
Amount]*0.03),IIf([DaysPastDue]<=0,CCur([Order Amount]*0.02),0))))
Click to expand...
 
Well then, what I really want is for it to just show Invoices within that
time period. Once it hits 30 days past due, then 2%
Once it hits 60 days 3%. Once it hits 90 days and over 4%. Once it hits
120 5%.
This is at least how I perceive it to be when you say 30,60,90,120 days
late.
Thanks,

Dave

Sean said:
Dave,
It is using 4% at 65 days because that is what you tell it to do and what
you told us you want:

"...60 to 89 days 4%..."

"...IIf([DaysPastDue]<=-61,CCur([Order
Amount]*0.04),..."

Sean

Dave Elliott said:
I realized that my code is not returning the result I want. If the
customer's bill is between 0 and 29 they pay the normal rate. If they
Click to expand...
are
30
to 59 days late, then they need to pay that %, which is 3%. The code below
does not do that. I think I need (2) pieces of code to accomplish this ?
zero to 29 days Original Invoice Amount. 30 to 59 Days, 3%, 60 to 89
Click to expand...
days
4%
and 90 days and over 4%. Right now the code will give me , say if the
Customer is late 65 days, which puts them into the 3% bracket , it adds the
4% amount instead. Why? The customers bill is $1260.00 and the 60 days
per-centage is $37.80 at 3%, but the formula gives it 4%, which is $50.00
making the total come to $1310.40. . .
Thanks, Dave

=IIf([DaysPastDue]<=-91,CCur([Order Amount]*0.04),[Order
Amount]+IIf([DaysPastDue]<=-61,CCur([Order
Amount]*0.04),IIf([DaysPastDue]<=-31,CCur([Order
Amount]*0.03),IIf([DaysPastDue]<=0,CCur([Order Amount]*0.02),0))))
Click to expand...
Click to expand...
 
Dave,

I get you now, you didn't mention the 5% fee on your first post so it read
as if you wanted 4% for both 60-90 AND 90 +. Anyway, try the following it
worked on my end:


NewAmount: (IIf([DaysPastDue]<29,[OrderAmount],IIf([DaysPastDue] Between 30
And 59,([OrderAmount]*1.02),IIf([DaysPastDue] Between 60 And
89,([OrderAmount]*1.03),IIf([DaysPastDue] Between 90 And
119,([OrderAmount]*1.04),IIf([DaysPastDue]>119,([OrderAmount]*1.05)))))))

This will give you the new amount due -- the OrderAmount plus the late fee.
Obviously, if you just want the 'late charge' then change the products from
1.0x to .0x (so 1.03 becomes .03).

Sean



Dave Elliott said:
Well then, what I really want is for it to just show Invoices within that
time period. Once it hits 30 days past due, then 2%
Once it hits 60 days 3%. Once it hits 90 days and over 4%. Once it hits
120 5%.
This is at least how I perceive it to be when you say 30,60,90,120 days
late.
Thanks,

Dave

Sean said:
Dave,
It is using 4% at 65 days because that is what you tell it to do and what
you told us you want:

"...60 to 89 days 4%..."

"...IIf([DaysPastDue]<=-61,CCur([Order
Amount]*0.04),..."

Sean

Dave Elliott said:
I realized that my code is not returning the result I want. If the
customer's bill is between 0 and 29 they pay the normal rate. If they
Click to expand...
are
30
to 59 days late, then they need to pay that %, which is 3%. The code below
does not do that. I think I need (2) pieces of code to accomplish this ?
zero to 29 days Original Invoice Amount. 30 to 59 Days, 3%, 60 to 89
Click to expand...
days
4%
and 90 days and over 4%. Right now the code will give me , say if the
Customer is late 65 days, which puts them into the 3% bracket , it
Click to expand...
adds
the
4% amount instead. Why? The customers bill is $1260.00 and the 60 days
per-centage is $37.80 at 3%, but the formula gives it 4%, which is $50.00
making the total come to $1310.40. . .
Thanks, Dave

=IIf([DaysPastDue]<=-91,CCur([Order Amount]*0.04),[Order
Amount]+IIf([DaysPastDue]<=-61,CCur([Order
Amount]*0.04),IIf([DaysPastDue]<=-31,CCur([Order
Amount]*0.03),IIf([DaysPastDue]<=0,CCur([Order Amount]*0.02),0))))
Click to expand...
Click to expand...
Click to expand...
 
With some thing as complex as this I would normally write my own VBA
function to return the amount + the added %. It makes it much easier to read
and find faults. Your DaysPastDue is also negative in this case -89 is not
less than -90 is is greater than. this could be your problem and causing the
errors. if you are calculating with something like DaysPastDue =
InvoiceDate - Now() then make it Now()-InvoiceDate or ABS(InvoiceDate -
Now() )

If your DaysPastDue is a positive number your VBA would look something like
this

Public Function AdjustedAmount(pDaysPastDue, pOrderAmount) As Currency

Select Case pDaysPastDue
Case 0 to 30
AdjustedAmount = pOrderAmount
Case 30 To 59
AdjustedAmount = pOrderAmount * 1.03
Case 60 To 89
AdjustedAmount = pOrderAmount * 1.04
Case Is >= 90
'if the amount added is the same here adjust make this >=60 and omit the
previuos case 60 to 89.
AdjustedAmount = pOrderAmount * 1.04
'Catch all anything pDaysPastDue less than 0 not a number will return 0 as
the amount to indicate an error
Case Else
AdjustedAmount = 0
End Select
End Function

Place this in a module and to use it in a query use

=AdjustedAmount([DaysPastDue] , [OrderAmount])

Happy programming
 
Back
Top