Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Apr 2009
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Tina,

    How about: MH Total = IIf ([Est Weight]>100,Int([Est Weight]/100)*[SRate],[SRate])
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Lounger
    Join Date
    Apr 2009
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I will try this right now and let you know how it works. Thanks so much.

    Tina

  4. #4
    Lounger
    Join Date
    Apr 2009
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    Lounger
    Join Date
    Apr 2009
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
  •