Setting limits in a caculated field 1

[clackl]

I have a calculated field in a query which I use to calculate postage according to weight. This has code in it to round the figure up or down to the nearest multiple of 5. What I would like to be able to do is to set an upper limit of 40 to the result that the calculation produces. So that no matter what the weight the calculation can only produce numbers up to a maximum of 40.

Is this possible?

 

[KenReay]

Hi

Sure is if you amend your formula appropriately

Without seeing your formulay one method would be

Postage:IIF(YourFormula>40,40,YourFormula)

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[clackl]

Thanks for the prompt reply. The forumula I am using to round to multiple of 5's is:
IIf(([num] Mod 5)>=3,[num]+(5-([num] Mod 5)),[num]-([num] Mod 5))

When I tried to use the formula that you had given me it highlights the comma after the 3 and gives the error message "You have entered an invalid comma or omitted quotation marks." Although run by itself the original code works well.

 

[KenReay]

hi

iif(IIf(([num] Mod 5)>=3,[num]+(5-([num] Mod 5)),[num]-([num] Mod 5))>40,40,IIf(([num] Mod 5)>=3,[num]+(5-([num] Mod 5)),[num]-([num] Mod 5)))

i think



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[clackl]

Perfect. Thank You.

 

[clackl]

I am wondering if it is possible to take this one step further.

I think I need to use a different example to make it clear.

If a medication is prescribed according to weight, then by multiplying weight by dose we get the daily requirements of the patient. But if the medication is only produced in set formats of XXXmg yyymg,or cccmg we need to allow a +/- 15% variability in the weight calculation so that we have manageable doses.

By using your calculation for the postage question I can get so far in this process, but is there a way of allowing for the +/- 15% variability in the weight and still produce an accurate dosage of the medication.