Greetings:I am a novice that is trying to calculate rental rates with price points based on duration of rentals.@ four days discount, etc. These individual statements work, but the series does not.I get an error indicator about random characters at the end of the first line.If I just use first line, it works great (for rentals of .5 day duration only).If([Rental Days]=0.5,[Rental Days]*[Rental Item - Daily Rate],0) If([Rental Days]>=1 or [Rental Days]<=3, [Rental Days]*[Rental Item - Daily Rate],0)If([Rental Days]>=4 or [Rental Days]<=24, [Rental Days]*[Rental Item - Weekly Rate],0)If([Rental Days]>=25, [Rental Days]*[Rental Item - Monthly Rate],0)Any kindly assist would be greatly appreciated.

Try this If( [Rental Days]=0.5,[Rental Days]*[Rental Item - Daily Rate], [Rental Days]>=1 or [Rental Days]=4 or [Rental Days]=25, [Rental Days]*[Rental Item - Monthly Rate])

You need to divide the range of min and max days into disjoint non-overlapping intervals that covers all possibilities.Try this:[Rental Days]*If( [Rental Days] <= 3, [Rental Item - Daily Rate], 3 < [Rental Days] and [Rental Days] <= 24, [Rental Item - Weekly Rate], 24 < [Rental Days] , [Rental Item - Monthly Rate])

Better yet:[Rental Days]*If( [Rental Days] <= 3, [Rental Item - Daily Rate], [Rental Days] <= 24, [Rental Item - Weekly Rate], [Rental Item - Monthly Rate])

Dan:Excellent. I really appreciate you taking the time.I think I need to use the ranges indicated below for correct pricing. Is my use of "AND" correct?[Rental Days]*If( [Rental Days] =.5, [Rental Item - Daily Rate], [Rental Days] >=1 AND <= 3, [Rental Item - Daily Rate], [Rental Days] >=4 AND <= 24, [Rental Item - Weekly Rate], [Rental Days] >24,[Rental Item - Monthly Rate])

Dan:Got it! "AND" operator doesn't work. The information you provided worked just fine (just a couple more similar lines to create the ranges).Thank you! Was getting frustrated.Dirk

Novice in need of help with rental rate formula

QuickBaseCoachD
Qrew Captain
7 years ago
Try this

If(
[Rental Days]=0.5,[Rental Days]*[Rental Item - Daily Rate],

[Rental Days]>=1 or [Rental Days]=4 or [Rental Days]=25, [Rental Days]*[Rental Item - Monthly Rate])
- QuickBaseCoachD
  Qrew Captain
  7 years ago
  Sorry, Dirk, I see that my iPhone answer got garbled. You seem to have this well in hand now.

_anomDiebolt_

Qrew Elite

7 years ago

You need to divide the range of min and max days into disjoint non-overlapping intervals that covers all possibilities.

Try this:

[Rental Days]
*
If(
                          [Rental Days] <=  3, [Rental Item - Daily Rate],
   3 < [Rental Days]  and [Rental Days] <= 24, [Rental Item - Weekly Rate],
  24 < [Rental Days]                         , [Rental Item - Monthly Rate]
)

_anomDiebolt_

Qrew Elite

7 years ago

Better yet:

[Rental Days]
*
If(
  [Rental Days] <=  3, [Rental Item - Daily Rate],
  [Rental Days] <= 24, [Rental Item - Weekly Rate],
  [Rental Item - Monthly Rate]
)

DirkRuana
Qrew Captain
7 years ago
Dan:

Excellent. I really appreciate you taking the time.
I think I need to use the ranges indicated below for correct pricing. Is my use of "AND" correct?

[Rental Days]
*
If(
[Rental Days] =.5, [Rental Item - Daily Rate],
[Rental Days] >=1 AND <= 3, [Rental Item - Daily Rate],
[Rental Days] >=4 AND <= 24, [Rental Item - Weekly Rate],
[Rental Days] >24,[Rental Item - Monthly Rate]
)
DirkRuana
Qrew Captain
7 years ago
Dan:

Got it! "AND" operator doesn't work. The information you provided worked just fine (just a couple more similar lines to create the ranges).

Thank you! Was getting frustrated.

Dirk
- RobIV
  Qrew Cadet
  7 years ago
  Dirk,
  
  Did you test Dan's solution? It should work without the ranges due to the order in which the program runs its evaluations, i.e. - Will check <=3 first, if that fails will move to <=24, if that fails will just charge monthly rate.
  
  TL;DR
  
  But, with that being said, you can use AND to enforce ranges, but you have to declare the object you are comparing values to each time.
  
  You have:
  
  ...
  [Rental Days] >=1 AND <= 3, [Rental Item - Daily Rate],
  ...
  
  You need:
  
  ...
  [Rental Days] >=1 AND [Rental Days] <= 3, [Rental Item - Daily Rate],
  ...
  
  for that to work.
  
  However; I urge you to be careful with specific ranges. If you do not consider all possibilities your code will break.
  
  For instance, your solution (with the AND operator) doesn't consider anything less than half a day (or a value between 0.5 and 1), or 3.5 days, or if someone submits the form without any days (blank), your code will break and the rate will not be set. Maybe this breaks nothing, maybe everything. Just be aware.
  
  Hope this helps sheds some light.
  
  ~Rob
- DirkRuana
  Qrew Captain
  7 years ago
  Rob,
  
  That does help!
  
  I was planning on limiting method of selecting number of days using numeric field in another table.
  
  Does that make sense?
  
  Dirk
DirkRuana
Qrew Captain
7 years ago
Thank you all for great help.

I think I am very close.

I am getting a error message: "Expecting number"

Did a screen grab (please see attached).

Any help is greatly appreciated.

Dirk
RobIV
Qrew Cadet
7 years ago
Syntax error. Remove the comma before the AND.
DirkRuana
Qrew Captain
7 years ago
Rob:

That was it. It works just fine now.

Thank you for helping a beginner!

Dirk
- RobIV
  Qrew Cadet
  7 years ago
  No problem. Keep hacking!

Forum Discussion

Novice in need of help with rental rate formula