if or syntax help needed

[techexpressinc]

I have to flip values for a field.



if the field Q12 has a 1 - I need to change it to a 5.
if the field Q12 has a 2 - I need to change it to a 4.
etc.

I have tried this without success.
Q12A: IIf([Q12]=1,5),Iff([Q12]=2,4)

Can someone point me to the right syntax?

Thanks
Russ

[RuralGuy]

And if it is neither value?

[techexpressinc]

I need to switch values on a field; values 0 thru 5.
0 stays 0
1 goes to 5
2 goes to 4
3 stays 3
4 goes to 2
5 goes to 5

I have coded this for a started for the concept but syntaxly failing.
Q12A: IIf([Q12]=1,5),Iff([Q12]=2,4)


Thx
Russ

[RuralGuy]

Create a Public Function in a Standard Module that uses a Select Case structure. It will be easier to understand and maintain. A nested IIF is hard to read. Do you know how to do that? Post back if you need further assistance.

[RuralGuy]

Is the field numeric (Integer, Long, Single, Double) or a string?

[RuralGuy]

Here's what it would look like if the field is an Integer:

Public Function MySwitch(InValue As Integer) As Integer
    Select Case InValue
        Case 1
            MySwitch = 5
        Case 2
            MySwitch = 4
        Case 3
            MySwitch = 3
        Case 4
            MySwitch = 2
        Case 5
            MySwitch = 1
        Case Else
            MySwitch = InValue
    End Select
        
End Function

[techexpressinc]

Number Integer is the field.

[techexpressinc]

Rural Guy - thanks for the code and idea I will attempt to implement it today and let you know how it goes.
Rus

[techexpressinc]

i could not figure out how to install the function so i coded it like this
Q12A: IIf(([Q12]=1),5,IIf(([Q12]=2),4)) .... etc

[RuralGuy]

You would have put the function I supplied in a standard module, not a form. Then in the query you simply put:
Q12A:=MySwitch([Q12])

[techexpressinc]

Do I click Database Tools - Visual Basic - insert module and paste it there?

[RuralGuy]

On the right side of the Create Ribbon is Module. Press it.

[techexpressinc]

Rural Guy - Good Job! - You came through again. Your module is installed and working. My code is this for the 6 values needing the flip.
" Q12AA: MySwitch([Q12]) "
Thx
Keep up the good work you are going down in Cyber History.
Russ

[RuralGuy]

And you are wiser today than yesterday. Aren't you glad you kept going? You have unleashed the power of code and modules.

[techexpressinc]

Yes. Glad I kept at it. The module creation process - i have tried and failed a half dozen times in the past with.
Russ