lookat value of two text boxes to deside value of a third (1 Viewer)

[bigmac]

hi all can you help please , I have three text boxes [size] [color] and [price],
I want to look at the value of [size] and if It is a 2 or 4 and the value of [color] is blue or red then it will change the value of [price] to a 1,

if both contain anything other than these values then [price] value will be a 0 any help please

 

[theDBguy]

Hi. You could try an IIf() statement. For example, [price] could have the following in its Control Source.

=IIf(([size]=2 OR [size]=4) AND ([color]=“red” OR [color]=“blue”),1,0)

 

[bigmac]

hi theDBguy, thank you for quick response , could I increase this to include more values in size and color by just adding another or?

 

[theDBguy]

Hi. You could certainly try but just be careful how you mix ANDs and ORs in the expression. You might not get the expected result.

 

[bigmac]

Hi. You could try an IIf() statement. For example, [price] could have the following in its Control Source.

=IIf(([size]=2 OR [size]=4) AND ([color]=“red” OR [color]=“blue”),1,0)

have tried this but does not work, any ideas please

 

[theDBguy]

Hi. What does "does not work" mean? Are you getting any errors? Can you maybe post a screenshot? It's hard to understand exactly what you mean by merely translating your words into expression logic because we could be mixing ANDs and ORs incorrectly.

 

[bigmac]

hi not getting any errors , just does not change the value in price, did not add any more OR's to statement, just tried your way first before I add any

 

[theDBguy]

Okay, so you might break it down to smaller bits first just to make sure each piece of the logic works. For example, to check if the price changes from 1 to 0 when only checking the size.


=IIf([size]=2 OR [size]=4,1,0)

 

[bigmac]

hi theDBguy, will try this later , have to go out for now , tell you later if it works

 

[theDBguy]

Ok. After trying it out, you could try testing it with colors next.


=IIf([color]="blue" OR [color]="red",1,0)

 

[bigmac]

Okay, so you might break it down to smaller bits first just to make sure each piece of the logic works. For example, to check if the price changes from 1 to 0 when only checking the size.


=IIf([size]=2 OR [size]=4,1,0)

hi theDBguy yes this part works fine

 

[theDBguy]

Hi. Did you try the color part too?

 

[bigmac]

yes this works also

 

[theDBguy]

Okay, if both works separately, then the problem is when we combine them. What do you get with the following?


=IIf([size]=2 OR [size]=4 OR [color]="red" OR [color]="blue",1,0)

 

[bigmac]

yes this is working

 

[theDBguy]

yes this is working

Okay. Glad to hear it worked. Now, you could try adding the rest using only ORs. I guess we didn't need to use ANDs. Cheers!

 

[Pat Hartman]

Based on your original explanation, the answer you said is working is actually incorrect:

=IIf([size]=2 OR [size]=4 OR [color]="red" OR [color]="blue",1,0)

Should be

=IIf(([size]=2 OR [size]=4) AND ([color]="red" OR [color]="blue"),1,0)

Your original statement indicated two separate conditions, BOTH of which had to be true.

Just FYI, this is a bad method to use because it requires code changes. You should create a table to summarize all the variables and then use dLookup() to get the price from the table. If you create a table, you can turn over maintenance of the pricing to users and not make them dependent on code changes unless of course you need a lifetime employment making this type of change.

 

[theDBguy]

Hi. Glad to hear it. I guess I misunderstood the original question. Good luck.

 

[isladogs]

Hi DB Guy
You correctly answered the original question in post 2 - same as Pat's later answer
But the OP actually wanted something else - your second solution