Whats wrong with this expression?

0
votes
Comments: Switch(((IIf(([qty_req]-[qty_on_hand])<0,0,([qty_req]-[qty_on_hand])))=0) And ((([qty_on_hand]-[qty_req])/[qty_req])<=0.2),"Please check manually")

I have been struggling with this expression for too long. I keep getting the error "This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables." I've tried breaking down the expression to see if there was a bracket I the wrong place but I can't figure this out.

Note: The word "Comments" is just the field name (I primarily use the Design View in MS Access).

Update - The goal behind this is to eventually add more conditions to this switch statement, but this first one isn't working so that's why it seems like it doesn't make sense to use a Switch. Also, in pseudo code, this is what the intention of this expression is:

Switch([TransferQTY]=0 And [Req is within 20% of Inventory], "Please check manually")

In regards to the first IIF statement:

IIf([Req-Inventory is negative, that means that we have enough on hand and don't need to send],0, [Req-Inventory])
2
sqlms-access
your parentheses are out of balance (specifically the first IIF statement). Also the AND does not make any sense where it is since it's not part of any logical expression. - D Stanley
can you tell us what this expression should do? - CeOnSql
@CeOnSql I updated the question to clarify what it needs to do. @DStanley in regards to the And - I tried to surround the segment before and after in parentheses in order to make sure the Switch statement understands the separation - Switch((A) And (B)) - whatwhatwhat
@lurker Um it's MS Access 2007-2010 if that answers your question? - whatwhatwhat
@lurker well....I am using Access as a front end. I like the Design view - whatwhatwhat

2 Answers

0
votes

I think it's simply a check like this:

IIf([qty_req]-[qty_on_hand]<0 And ([qty_on_hand]-[qty_req])/[qty_req]<=0.2,"Please check manually","") AS Comments
0
votes

The first IIF is just strangely built and has some redundancy to it. The second might give you strange answers because you don't have parans around your numerator. As it's written it could be simplified to:

As for the first IIF, you stated

"IIf([Req-Inventory is negative, that means that we have enough on hand and don't need to send],0, [Req-Inventory])"

in the context of the switch (psuedo-coded):

Switch([TransferQTY]=0 And [Req is within 20% of Inventory], "Please check manually")

This is basically saying "If the quantity requested minus thequantity on hand is less than or equal to 0", so instead of an IIF to do the "Less than or equal to" bit, just use <=:

Switch(((qty_req - qty_on_hand) <= 0) AND (((qty_on_hand - qty_req)/qty_req) <= 0.2), "Please Check Manually")

This will work better because Access is balking about the complexity. This dramatically reduces the complexity and accomplishes the same thing.

Also, I've gone a little heavy handed with the parantheses here. You could remove the ones that delineate each of the conditions that the AND function is evaluating and it would be fine.

I've removed the bit here about not using switch that was in a previous version of this answer since OP stated that switch() will be used after this bit starts working.