Need Help regarding this Iff statement (1 Viewer)

J

johnherrerajuan

Registered User.
Local time
Today, 03:31
Joined
Feb 25, 2013
Messages
44
I entered this iff statement and it says expression you entered is to complex can someone give me advice on how to approach this. Do you think i should split the formula

Thank you

IIf([MarkUpI]=100 And [Stock/NonStock1]="Stock",[QTY1]+[UnitPrice1]*1.22, IIf([MarkUpI]=101 And [Stock/NonStock1]="Stock",[QTY1]+[UnitPrice1]*1.22, IIf([MarkUpI]=200 And [Stock/NonStock1]="Stock",[QTY1]+[UnitPrice1]*1.22, IIf([MarkUpI]=201 And [Stock/NonStock1]="Stock",[QTY1]+[UnitPrice1]*1.22, IIf([MarkUpI]=300 And [Stock/NonStock1]="Stock",[QTY1]+[UnitPrice1]*1, IIf([MarkUpI]=400 And [Stock/NonStock1]="Stock",[QTY1]+[UnitPrice1]*1.05, IIf([MarkUpI]=500 And [Stock/NonStock1]="Stock",[QTY1]+[UnitPrice1]*1.03, IIf([MarkUpI]=600 And [Stock/NonStock1]="Stock",[QTY1]+[UnitPrice1]*22, IIf([MarkUpI]=100 And [Stock/NonStock1]="Non-Stock",[QTY1]+[UnitPrice1]*1.22, IIf([MarkUpI]=101 And [Stock/NonStock1]="Non-Stock",[QTY1]+[UnitPrice1]*1.05, IIf([MarkUpI]=200 And [Stock/NonStock1]=Non-Stock,[QTY1]+[UnitPrice1]*1.22, IIf([MarkUpI]=201 And [Stock/NonStock1]=Non-Stock,[QTY1]+[UnitPrice1]*1.05, IIf([MarkUpI]=300 And [Stock/NonStock1]=Non-Stock,[QTY1]+[UnitPrice1]*1, IIf([MarkUpI]=400 And [Stock/NonStock1]=Non-Stock,[QTY1]+[UnitPrice1]*1.05, IIf([MarkUpI]=500 And [Stock/NonStock1]=Non-Stock,[QTY1]+[UnitPrice1]*1.03, IIf([MarkUpI]=600 And [Stock/NonStock1]=Non-Stock,[QTY1]+[UnitPrice1]*22,0)))))))))))))))
 
pr2-eugin

pr2-eugin

Super Moderator
Local time
Today, 11:31
Joined
Nov 30, 2011
Messages
8,494
Hello John.. That is a lot of IIF, and a whole lot of brackets..

But, You are missing a Final closing parentheses, by the looks of it.. Which might be the reason for error.. Also you are missing some "" in some of the IIF conditions.. Try the following..
Code: 
IIf([MarkUpI]=100 And [Stock/NonStock1]="Stock",[QTY1]+[UnitPrice1]*1.22, IIf([MarkUpI]=101 And [Stock/NonStock1]="Stock",[QTY1]+[UnitPrice1]*1.22, IIf([MarkUpI]=200 And [Stock/NonStock1]="Stock",[QTY1]+[UnitPrice1]*1.22, IIf([MarkUpI]=201 And [Stock/NonStock1]="Stock",[QTY1]+[UnitPrice1]*1.22, IIf([MarkUpI]=300 And [Stock/NonStock1]="Stock",[QTY1]+[UnitPrice1]*1, IIf([MarkUpI]=400 And [Stock/NonStock1]="Stock",[QTY1]+[UnitPrice1]*1.05, IIf([MarkUpI]=500 And [Stock/NonStock1]="Stock",[QTY1]+[UnitPrice1]*1.03, IIf([MarkUpI]=600 And [Stock/NonStock1]="Stock",[QTY1]+[UnitPrice1]*22, IIf([MarkUpI]=100 And [Stock/NonStock1]="Non-Stock",[QTY1]+[UnitPrice1]*1.22, IIf([MarkUpI]=101 And [Stock/NonStock1]="Non-Stock",[QTY1]+[UnitPrice1]*1.05, IIf([MarkUpI]=200 And [Stock/NonStock1]="Non-Stock",[QTY1]+[UnitPrice1]*1.22, IIf([MarkUpI]=201 And [Stock/NonStock1]="Non-Stock",[QTY1]+[UnitPrice1]*1.05, IIf([MarkUpI]=300 And [Stock/NonStock1]="Non-Stock",[QTY1]+[UnitPrice1]*1, IIf([MarkUpI]=400 And [Stock/NonStock1]="Non-Stock",[QTY1]+[UnitPrice1]*1.05, IIf([MarkUpI]=500 And [Stock/NonStock1]="Non-Stock",[QTY1]+[UnitPrice1]*1.03, IIf([MarkUpI]=600 And [Stock/NonStock1]="Non-Stock",[QTY1]+[UnitPrice1]*22,0))))))))))))))))
However you might need to consider creating a custom function to do this calculation for you.. It would be simple and efficient, and a lot less messy.. IMO
 
Last edited:
J

johnherrerajuan

Registered User.
Local time
Today, 03:31
Joined
Feb 25, 2013
Messages
44
Ok I see, but how would I create a custom function. Can you be a little more specific.

pr2-eugin said:
Hello John.. That is a lot of IIF, and a whole lot of brackets..

But, You are missing a Final closing parentheses, by the looks of it.. Which might be the reason for error..

However you might need to consider creating a custom function to do this calculation for you.. It would be simple and efficient, and a lot less lines of code.. IMO
Click to expand...
 
pr2-eugin

pr2-eugin

Super Moderator
Local time
Today, 11:31
Joined
Nov 30, 2011
Messages
8,494
When you create a custom function, you would pass the two values that you use in your IIF condition and get the multiplying factor.. which would either be 1, 1.03, 1.05, 1.22, 22.. I think I have carefully went through and constructed this.. see if you get the right result..

To make this work, copy the code into a new/pre existing module, save, compile..
Code: 
Public Function getResult(MarkUpI As Integer, StockDet As String) As Double
    Dim mulFactor As Double
    If StockDet = "Stock" Then
        Select Case MarkUpI
            Case 100,101,200,201
                mulFactor = 1.22
            Case 300
                mulFactor = 1
            Case 400
                mulFactor = 1.05
            Case 500
                mulFactor = 1.03
            Case 600
                mulFactor = 22
            Case Else
                mulFactor = 0
        End Select
    Else
        Select Case MarkUpI
            Case 100,200
                mulFactor = 1.22
            Case 101,201,400
                mulFactor = 1.05
            Case 300
                mulFactor = 1
            Case 500
                mulFactor = 1.03
            Case 600
                mulFactor = 22
            Case Else
                mulFactor = 0
        End Select
    End If
    getResult = mulFactor
End Function
To use the function in your Query.. Just have..
Code: 
someNameToField : [QTY1] + [UnitPrice1] * getResult([MarkUpI], [Stock/NonStock1])
 
J

johnherrerajuan

Registered User.
Local time
Today, 03:31
Joined
Feb 25, 2013
Messages
44
But i have a quick question can i enter this in a calculated field on my table
 
pr2-eugin

pr2-eugin

Super Moderator
Local time
Today, 11:31
Joined
Nov 30, 2011
Messages
8,494
johnherrerajuan said:
But i have a quick question can i enter this in a calculated field on my table
Click to expand...
That would be a No, unfortunately.. I know, sometime it is tempting to save it.. but Calculated Fields are meant to be on Queries not in tables.. More information on 'Why' could be found in Allen Browne's site..

You can use the function to display on a Form, with the help of an Unbound Text box.. Queries/Reports.. but most certainly not in tables..
 
You must log in or register to reply here.

Similar threads

L
Nested IIF Statement in expression builder
Replies
5
Views
4,020
Galaxiom
G
D
Iff Statement in Criteria
Replies
8
Views
1,253
Frothingslosh
Frothingslosh
D
Function with Case
Replies
12
Views
1,243
namliam
N
Rx_
Quarter-Quarter Formula for Township Does anyone else do this?
Replies
0
Views
1,005
Rx_
Rx_
Rx_
Date Difference - options for Sec Min Hr Days Yr
Replies
0
Views
4,296
Rx_
Rx_

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom