# Thread: IIf statement too large

1. Hello, I am obviously not using the most effective way to create a calculation. I have a query that the subform feeds off of. In the query I have a calculation that calculates the Total Material Handling Charge. This field (MH Total) is based upon the EST Weight field and the Rate field. The Rate field and the EST Weight fields are both typed in. The MH Total calculates based upon the weight all the way up to 5000. Example: MH Total = IIf ([Est Weight]>5000,50*[SRate],IIf([Est Weight]>4900,49*[SRate], etc.
This calculation goes all the way down to 100 and anything less than 100, in increments of 100. My problem is that the calculation is too big for a field in a query and gets truncated off at 2100. Is there an easier way to do this calculation? I need this calculation in the query because I need it to show up on the form and also on a report later.

Any help would be appreciated.

Thanks,
Tina

2. Tina,

How about: MH Total = IIf ([Est Weight]>100,Int([Est Weight]/100)*[SRate],[SRate])

3. I will try this right now and let you know how it works. Thanks so much.

Tina

4. Hi, it partially worked, and that part was brilliant. It probably was my fault, not explaining clear enough.

For example, if the number is 2600 then it would be 26 * SRate, but if it is over 2600, say 2601, then it is now 27 * SRate.
Some things to know:
1. The minimum is 200 lbs, so if it was less than or equal to 200, then it would be 2 * the SRate
2. Greater than 200, 3 * SRate
3. Greater than 300, 4 * SRate
4. Etc. all the way up to 5000, so if it is less than or equal to 5000 then 50 * SRate, but if it is greater than 5000, then 60 * SRate.

Do you have a solution for this? The first one was great, except for accounting for anything greater than the rounded number (eg. 2600).

Thanks so much,

Tina

5. Tina,

Try this one: How about: MH Total = IIf([Est Weight] > 200, Int(([Est Weight] + 99) / 100) * [SRate], 2 * [SRate])

Here's the VBA code I used to test this just in case you're interested.
Code:
```Sub MathTest()

Dim cMH As Currency
Dim iWeight As Integer
Dim cRate   As Currency

cRate = 3#
iWeight = InputBox("Enter Weight")

cMH = IIf(iWeight > 200, Int((iWeight + 99) / 100) * cRate, 2 * cRate)

MsgBox "Weight: " & Format(iWeight) & vbCrLf & _
"Charge: " & Format(cMH, "\$#,###.00")
End Sub```

6. Thank you so much. It worked. You are brilliant. I really, really appreciate it every so much.

Thank you,

Tina

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•