help with formulas

A

antonov

Hello,
I have a form and in this form I have 4 text boxes (which I will call
fields). In the first field there is a formula that reads:
=IIf(DateDiff("d",[Date],[Out])<=2,0,DateDiff("d",[Date],[Out]+1)*[ChargWeight]*0.015)
this works ok but.... it should work only if the date difference is up to 15
days because then...
field 2 should take over and calculate the whole difference at 0.03 and not
0.015.... over 30 days and up to 365 field 3 should take over at a rate of
0.050 and over 365 field 4 at a rate of 0.070. Catch 21: for all 4 fields a
min. of $3 (if date difference is >3) should apply.....
 
J

John Vinson

Hello,
I have a form and in this form I have 4 text boxes (which I will call
fields). In the first field there is a formula that reads:
=IIf(DateDiff("d",[Date],[Out])<=2,0,DateDiff("d",[Date],[Out]+1)*[ChargWeight]*0.015)
this works ok but.... it should work only if the date difference is up to 15
days because then...
field 2 should take over and calculate the whole difference at 0.03 and not
0.015.... over 30 days and up to 365 field 3 should take over at a rate of
0.050 and over 365 field 4 at a rate of 0.070. Catch 21: for all 4 fields a
min. of $3 (if date difference is >3) should apply.....
Click to expand...

Sounds like you might want to write a custom VBA function to do this.
You could do it with nested IIF's or a complex call to the Switch()
function, but either one may exceed the allowable length of an
expression!

Air code:

Public Function RateByDate(Date1 As Date, Date2 As Date, Weight As
Long) As Currency
Dim iDays As Integer
Dim cTemp As Currency
iDays = DateDiff("d", Date1, Date2)
Select Case iDays
Case <= 2
cTemp = 0
Case <= 15
cTemp = Weight * 0.015 * (iDays + 1)
Case ... <etc etc>
Case Else
<handle the unexpected values>
End Select
If cTemp > 0 and CTemp < 3.0 then cTemp = 3.0
RateByDate = CTemp
End Function


John W. Vinson[MVP]
 
Top