Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    nick243 is offline Competent Performer
    Windows 7 64bit Access 2002
    Join Date
    May 2016
    Posts
    185

    IIf Function returns blank Cell if Like function is not met

    Weight Cost TI: IIf([Sheet1.Manifest] Like "TI*",IIf([lbs]<1000,[lbs]*0.28,IIf([lbs]>1000,[lbs]*0.22,IIf([lbs]>5000,[lbs]=1100,0)))))



    How can I force this IIf function to return a 0.00 cost if the Like is not met? I have tried the NZ function but have not had good results if the Like function is met.

    Thanks,
    Nick

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you should make 2 queries,
    1 for TI* , this will eliminate an IIF, then you can use the other IIF to calc lbs.

    the 2nd query for non TI records can give you a different LBS result.
    put both queries into a single UNION query

  3. #3
    nick243 is offline Competent Performer
    Windows 7 64bit Access 2002
    Join Date
    May 2016
    Posts
    185
    Ok, so I tried a union query and my results came back with every record duplicated? Every other line is blank in the weight cost iif statement. How does this happen?

    Thanks,
    Nick

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Are you doing this in a query? It so, I think it can be done pretty easily with a single calculation. Try this:
    Code:
    Weight Cost TI: IIf(Left([Sheet1.Manifest],2)="TI",IIf([lbs]>5000,1100,IIf([lbs]>1000,[lbs]*0.22,[lbs]*0.28)),0)

  5. #5
    nick243 is offline Competent Performer
    Windows 7 64bit Access 2002
    Join Date
    May 2016
    Posts
    185
    Here is my set up, I have to calculate based on the manifest number either TI or TRI that will determine weather I use the TRI calculation or the TI calculations. Depending on the weight of the Purchase order is how much I charge per pound. I want to be able to create reports off of this. Does this help?

    Thanks,
    Nick

  6. #6
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    You could keep building off the formula I provided (did you try it?) nesting in more levels.
    However, I do see a flaw in your logic. It appears that you would charge more for 999 lbs (999*0.28) than you would for 1001 lbs (1001*0.22).

    For complex calculations, rather than trying to write some long convoluted formula, I often prefer to create my own User Defined Function in VBA. They tend to be much easier to maintain. And if you ever need to make changes, you only have to make them in one place (as opposed to possibly have to make them in multiple calculations).

  7. #7
    nick243 is offline Competent Performer
    Windows 7 64bit Access 2002
    Join Date
    May 2016
    Posts
    185
    TI calcs
    0-999 * .28
    1000-4999 * .22
    >=5000 = 1100

    TRI Calcs
    0-999 *0.42
    1000-4999 *.39
    >5000 *.37
    >14000=5180

  8. #8
    nick243 is offline Competent Performer
    Windows 7 64bit Access 2002
    Join Date
    May 2016
    Posts
    185
    I will give it a try I am not very familiar with everything you put.

  9. #9
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I will give it a try I am not very familiar with everything you put.
    I basically used all the same functions you did, except I used LEFT instead of LIKE, and changed the order so it does what you originally asked).

    What exactly do your different Manifest values look like?
    Are they exactly "TI" and "TRI", or is that just a prefix?
    What other values may they contain?

  10. #10
    nick243 is offline Competent Performer
    Windows 7 64bit Access 2002
    Join Date
    May 2016
    Posts
    185
    Here is an example

    TRI-368878
    TI-385665

  11. #11
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    So, there is always a dash after the prefix.
    Are "TRI" and "TI" the only possible prefixes, or might there be something else?

  12. #12
    nick243 is offline Competent Performer
    Windows 7 64bit Access 2002
    Join Date
    May 2016
    Posts
    185
    Ok Ya it worked thank you. Putting the 0 before the last ) worked. Thank you for the help.

  13. #13
    nick243 is offline Competent Performer
    Windows 7 64bit Access 2002
    Join Date
    May 2016
    Posts
    185
    Quote Originally Posted by JoeM View Post
    So, there is always a dash after the prefix.
    Are "TRI" and "TI" the only possible prefixes, or might there be something else?
    From what I have seen it is only

    TI- or TRI-

  14. #14
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    OK. Here is a UDF that should do what you want:
    Code:
    Function WeightCost(manifest As String, lbs As Double) As Double
    
        Dim prfx As String
        
    '   Get prefix before dash
        If InStr(manifest, "-") > 1 Then
            prfx = Left(manifest, InStr(manifest, "-") - 1)
        End If
        
        Select Case prfx
    '       TI calculations
            Case "TI"
                Select Case lbs
                    Case Is < 1000
                        WeightCost = lbs * 0.28
                    Case Is < 5000
                        WeightCost = lbs * 0.22
                    Case Else
                        WeightCost = 1100
                End Select
    '       TRI calculations
            Case "TRI"
                Select Case lbs
                    Case Is < 1000
                        WeightCost = lbs * 0.42
                    Case Is < 5000
                        WeightCost = lbs * 0.39
                    Case Is < 14000
                        WeightCost = lbs * 0.37
                    Case Else
                        WeightCost = 5180
                End Select
        End Select
    
    End Function
    So, you just use this like any other function. It has two variables, Manifest and lbs. So to call it would look something like this:
    Total_Weight_Cost: WeightCost([Manifest],[lbs])

  15. #15
    nick243 is offline Competent Performer
    Windows 7 64bit Access 2002
    Join Date
    May 2016
    Posts
    185
    Thank you so much, this looks awesome. I will work this in.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Dir function vbdirectory returns a single dot
    By kallm in forum Programming
    Replies: 13
    Last Post: 07-27-2016, 04:33 AM
  2. Function that returns an array
    By lefty2cox in forum Programming
    Replies: 6
    Last Post: 03-03-2016, 04:34 PM
  3. Val function returns #error on text/percentage
    By allenjasonbrown@gmail.com in forum Queries
    Replies: 1
    Last Post: 06-23-2013, 08:24 AM
  4. Aggregate function returns duplicate values
    By lokiluke in forum Queries
    Replies: 3
    Last Post: 09-16-2011, 09:40 AM
  5. Replies: 6
    Last Post: 01-07-2011, 12:50 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums