?
Solved

Multiple If criteria in one expression vs Code builder

Posted on 2008-06-18
7
Medium Priority
?
726 Views
Last Modified: 2010-04-21
Is it possible to write one expression in an Access query where both criteria need to be met, two fields need to be updated and then one common exception if either of the conditions are not met?  For example, see below expression:

X: IIf([Master Table]![Purchasing Price with Markup]>0,[Master Table]![Purchasing Price with Markup]*[Master Table]![System Quantity],IIf([Master Table]![Cost Per Unit]>0,[Master Table]![System Quantity]*[Master Table]![Cost Per Unit],"Neither"))

I want to avoid having to use VBA code for this expression if possible, but understand it may be necessary.

Thanks
0
Comment
Question by:dabdowb
  • 3
  • 3
7 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 21817332
if you want two conditions met use { AND }

x:iif([A]>0 AND [B]>0, "xxx", "yyy")
0
 
LVL 75
ID: 21817337
Take a look at the Switch() Function instead of using IIF ...

mx
0
 

Author Comment

by:dabdowb
ID: 21817383
I actually found out about the AND feature, BUT the key change here is that there are two executions taking place if both conditions are met, as opposed to one.  So, if both fields are greater than zero, then I have two seperate calculations that need to take place, but then if either of those conditionals are not greater than zero, only one result needs to be given for both.

In the meantime, I will check out this switch function.

Thanks,
Matt
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft Access MVP) earned 2000 total points
ID: 21817394
From the Help File:

Switch Function
     
Evaluates a list of expressions and returns a Variant value or an expression associated with the first expression in the list that is True.

Syntax

Switch(expr-1, value-1[, expr-2, value-2 & [, expr-n,value-n]])

The Switch function syntax has these parts:

Part Description
expr Required. Variant expression you want to evaluate.
value Required. Value or expression to be returned if the corresponding expression is True.

Remarks

The Switch function argument list consists of pairs of expressions and values. The expressions are evaluated from left to right, and the value associated with the first expression to evaluate to True is returned. If the parts aren't properly paired, a run-time error occurs. For example, if expr-1 is True, Switch returns value-1. If expr-1 is False, but expr-2 is True, Switch returns value-2, and so on.

Switch returns a Null value if:

None of the expressions is True.

The first True expression has a corresponding value that is Null.
Switch evaluates all of the expressions, even though it returns only one of them. For this reason, you should watch for undesirable side effects. For example, if the evaluation of any expression results in a division by zero error, an error occurs.
0
 

Author Closing Comment

by:dabdowb
ID: 31468584
Thanks again for your help.  Sorry for the delay, I was working with a co-worker on this project and waiting for them to do some testing before I could give the thumbs up or down.  Cheers.
0
 

Author Comment

by:dabdowb
ID: 21977516
Thanks again for your help. Sorry for the delay, I was working with a co-worker on this project and waiting for them to do some testing before I could give the thumbs up or down. Cheers.
0
 
LVL 75
ID: 21979316
no problem
thank you ..

mx
0

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
With the functions here, you can parse, convert, and format back and forth between feet and inches and fractions and decimal inches - for normal as well as extreme values and with extreme precision.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

590 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question