IIF Statement - Returning Text and Numbers

Hi All!

I am trying to to use a IIF statement in an expression in my Query that will return both Numbers and Text.

I think I may have the format wrong, so any help will be appreciated.

I have nested IIF statements that evaluate a person's score, and if they get a certain score then I want it to return a dollar amount (which I have made it do), but then if they get a very low score I want it to return a text string "See Your Manager".

Below is an example:

Field: Final_Pay: IIF([Final_Score]=100,19,IIF([Final_Score]>50,18,"See Your Manager"))
Total: Expression

I had the format set to currency, and it will return the number values as $19.00 and $18.00, but for the "See Your Manager" it returns #Error.

I tried changing the format to "Standard" and got the same results, and then I tried "Fixed" and still the same results.

Any help will be appreciated to setting the format so it will return both numbers and text.

Thanks in advance!


Note: I am a beginner with Access, so I hope this isn't a silly question.

Hello,

Access is a purist regarding formats. It's returning an error because it's not a number value.

Cheers,
Diana

Originally Posted by dmang1

Hello,

Access is a purist regarding formats. It's returning an error because it's not a number value.

Cheers,
Diana

Thanks Diana! As a work around, could I change the format to text and then put in the following formula and work?

IIF([Final_Score]=100,"$19.00",IIF([Final_Score]>50,"$18.00","See Your Manager"))

I'm not sure how or if I can change an expression that deals with numbers in to a Text Format, since the only options it gives me in the Property Sheet for Format are all number formats.

Thanks all for your help!

Thanks all for your help!

After messing around with it on and off for 2 days, I found a solution to my problem.

As previously pointed out, I couldn't use both a number and text format in the query.

So I just had the query return 0 for the results that I wanted as text
IIF([Final_Score]=100,19,IIF([Final_Score]>50,18,0))

..... and then in the Report I entered the following formula:

=IIF([Final_Score]=0,"See Your Manager",[Final_Score])
On the Property Sheet under the Format tab, I set the Format to be Currency with Decimal Places 2, and then under the Data tab, I set the Text Format to Plain Text, and it works PERFECTLY YAYY

Thanks all!