Conditional query field

[jgnasser]

I would like to create a calculated field in a query that returns different values based on values of other fields. I did not succeed in entering a conditional statement in the query grid. For example, here are my fields and values:

Fld1 Fld2 Fld3 Calculated(conditional)
-1 0 0 "Text1"
0 -1 0 "Text2"
0 0 -1 "Text3"
0 -1 0 "Text2"

In essence my calculated filed will be running a conditional test like

IF Fld1.Value = -1 then
calculated = "Text1"
Elseif Fld2.Value = -1 then
calculated = "Text2"
...........etc

Is this possible? How?

 

[dcx693]

This is definitel possible, but the method you should use depends on the complete rules you wish to follow.

You can either use a series of nested IIf statement, or write yourself a custom function. I answered a question recently here: Calculated Field Expression

 

[jgnasser]

I went the VBA way and was extremely impressed. Thanks for the tip.

 

[mdbBound]

Creating expression field based on diff criterias from another field

Hello

You said VBA is better for this.

So how would you do it in VBA. I did not follow the Iif version above.

Thanks

 

[dcx693]

"VBA" means he/she wrote a VBA custom function. See the example in my link.