IIF Function

[SoxPats83]

I am running into a problem with the IIf function. Below is my code, what i am trying to do is in a text box on a form, with the control source applied to another field, i want to take the numeric value and convert that into a word.

=IIf([Example]="1", "Example",IIf([Example]=2,"Example",IIf([Example]=3,"Example",IIf([Example]=4,"Example",IIf([Example]=5,"Example","none")))))

If anyone can help, I would appreciate it. thanks.

 

[SoxPats83]

funny thing now is for records yet to have any values in them, the "None" is populating, but nothing else

 

[boblarson]

If records have no values in them, then NONE would be populated because it is what is in the FALSE statement.

 

[boblarson]

And I think I would use the SWITCH function:

=Switch([Example]=1, “Example 1”, [Example]=2, “Example 2”, [Example]=3, “Example 3”, [Example]=4, “Example 4”, [Example]=5, “Example 5”, [Example] Not In(1,2,3,4,5),“None”)

 

[SoxPats83]

And I think I would use the SWITCH function:

=Switch([Example]=1, “Example 1”, [Example]=2, “Example 2”, [Example]=3, “Example 3”, [Example]=4, “Example 4”, [Example]=5, “Example 5”, [Example] Not In(1,2,3,4,5),“None”)

that is an interesting alternative. I have never heard of the switch functiom. Do you have any additional info about SWITCH? Also, in your line you had, "[Example]=1, "Example 1"", that would return Example 1, right?

 

[boblarson]

Also, in your line you had, "[Example]=1, "Example 1"", that would return Example 1, right?

Yes, it would. In your example if you wanted the SAME word returned you could make it even shorter:

=Switch([Example] In (1,2,3,4,5), "Example", [Example] Not In(1,2,3,4,5), "none")

But do you want the value for the FIELD example if 1,2,3,4,5? If so it would be:

=Switch([Example] In (1,2,3,4,5), [Example], [Example] Not In(1,2,3,4,5), "none")

 

[SoxPats83]

Yes, it would. In your example if you wanted the SAME word returned you could make it even shorter:

=Switch([Example] In (1,2,3,4,5), "Example", [Example] Not In(1,2,3,4,5), "none")

But do you want the value for the FIELD example if 1,2,3,4,5? If so it would be:

=Switch([Example] In (1,2,3,4,5), [Example], [Example] Not In(1,2,3,4,5), "none")

Basically what i am looking for is if the number 1 is inputed, a specific word would be returned. the number 2, a different words, and so on...

 

[boblarson]

So the initial Switch I used is what you would use and just replace "Example 1", "Example 2" etc. with your words.

 

[SoxPats83]

So the initial Switch I used is what you would use and just replace "Example 1", "Example 2" etc. with your words.

Thank You! This will be a huge help.

 

[SoxPats83]

unfortunately this switch function is not working for me

 

[SoxPats83]

i have been typing this expression into the Default Value box within this field. would it be possible that is the wrong place?

 

[boblarson]

i have been typing this expression into the Default Value box within this field. would it be possible that is the wrong place?

Yep, wrong place. It goes in the CONTROL SOURCE of the text box on the form.

 

[SoxPats83]

thank you very much. that did the trick. now i am running into some text empty boxes say "#Error" and some do not... kinda weird.

 

[boblarson]

No, probably null Fields. You need to use the NZ function to handle them like:

=NZ(Switch([Example]=1, “Example 1”, [Example]=2, “Example 2”, [Example]=3, “Example 3”, [Example]=4, “Example 4”, [Example]=5, “Example 5”, [Example] Not In(1,2,3,4,5),“None”, "None"))

 

[SoxPats83]

boblarson, your help is always appreciated, but sadly i may be missing something. i added the NZ at them beginning and the repeated "none" at the end, but then it gave me the "#Error" in every field...

 

[boblarson]

boblarson, your help is always appreciated, but sadly i may be missing something. i added the NZ at them beginning and the repeated "none" at the end, but then it gave me the "#Error" in every field...

My bad - I didn't do it correctly. I think we need an IIF:


=IIf(Len([Example] & "")=0, "None",Switch([Example]=1, “Example 1”, [Example]=2, “Example 2”, [Example]=3, “Example 3”, [Example]=4, “Example 4”, [Example]=5, “Example 5”, [Example] Not In(1,2,3,4,5),“None”))

 

[SoxPats83]

i have the below expression that is giving me an error with the syntax:

=IIf(Len([Example] & "")=0,"none",Switch([Example]=1,"Example 1",[Example]=2,"Example 2",[Example]=3,"Example 3",[Example]=4,"Example 4",[Example]=5,"Example 5",[Example]=6,"Example 6" [Example] Not In(1,2,3,4,5,6), "none"))

more specifically the error says: "The expression you entered contains invalid syntax. You may have entered an operand without an operator."

 

[boblarson]

We're missing a comma (see the big red comma):

=IIf(Len([Example] & "")=0,"none",Switch([Example]=1,"Example 1",[Example]=2,"Example 2",[Example]=3,"Example 3",[Example]=4,"Example 4",[Example]=5,"Example 5",[Example]=6,"Example 6", [Example] Not In(1,2,3,4,5,6), "none"))

 

[SoxPats83]

We're missing a comma (see the big red comma):

=IIf(Len([Example] & "")=0,"none",Switch([Example]=1,"Example 1",[Example]=2,"Example 2",[Example]=3,"Example 3",[Example]=4,"Example 4",[Example]=5,"Example 5",[Example]=6,"Example 6", [Example] Not In(1,2,3,4,5,6), "none"))

big time oversight. thank you. so now what i am getting is some null fields come back with "none" and other's still come back with "#Error". you are really helping me out here though, sorry for being a pain.

 

[boblarson]

big time oversight. thank you. so now what i am getting is some null fields come back with "none" and other's still come back with "#Error". you are really helping me out here though, sorry for being a pain.

I think we are now down to having to have you upload a copy of the database so I can actually check to see what is happening.