calculated field

[neeraj]

I am creating a new field say, NewField in a query which uses other fields.
I want the logic to be like this:
If Field1=val1 and Field2=val2 and Field3=val3 then NewField = val1-val2
If Field1=val1 and Field2=val2 and Field3=val4 then New Field = val1-val2/100
If Field1=val1 and Field2=val2 and Field3=val5 then New Field = val5*val2
Basically, val1 and val2 dont change and Field 3 is like a case statement
and depending upon the case value, the operation for calculation of NewField
changes. How can I create this NewField in Access

 

[Ofer]

Using the iif, this is how it should look like

iif(Field1=val1 and Field2=val2 , iif(Field3=val3 , val1-val2,
iif(Field3=val4 , val1-val2/100, iif(Field3=val5 , val5*val2,0))),0) as
NewField

If any of the conditions doesn't apply I assign 0.

 

[John Spencer (MVP)]

You can use a nested IIF statement or a SWITCH statement

NEWField: SWITCH(Field1=val1 and Field2=val2 and Field3=val3, Val1-val2,
Field1=val1 and Field2=val2 and Field3=val4, val1-val2/100,
Field1=val1 and Field2=val2 and Field3=val5, val5*val2)

WIth your example, you could combine IIF and the Switch function to

NewField: IIF(Field1=val1 and Field2=val2,
SWITCH(Field3=val3, Val1-val2,
Field3=val4, val1-val2/100,
Field3=val5, val5*val2),Null)

You didn't say what you wanted if none of the criteria matched, so I assumed Null

 

[neeraj]

It worked, thanks, whats the syntax of the switch field. I couldn't find ot
in access help

 

[John Spencer (MVP)]

Switch(TestCondition,ValueToReturn,TestCondition,ValueToReturn, ...)

It steps through until the first TestCondition returns True and then returns the
associated ValueToReturn.

Try Opening up VBA and looking in that help as Switch is a VBA function.

 

[neeraj]

If none of the conditions in the switch statement is met, I want the
calculated field to assume a specific value say Val10. What would be the
syntax for that

 

[John Spencer]

Add one pair to the switch statement. TRUE, WhatEverValue.

SWITCH returns the second half of the pair, when the first half of the pair
evaluates to True. It does this for the first time it detects True.

If I understand what you want to do, you would end up with something like.

NewField: IIF(Field1=val1 and Field2=val2,
SWITCH(Field3=val3, Val1-val2,
Field3=val4, val1-val2/100,
Field3=val5, val5*val2,
True,Val10),Null)