#  Other Applications & Softwares  > Access Tables & Databases >  > [SOLVED] IIF Statement - Returning Text and Numbers

## rmikulas

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.

----------


## dmang1

Hello,

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

Cheers,
Diana

----------


## rmikulas

> 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.  :Frown:  

Thanks all for your help!

----------


## rmikulas

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  :Smilie:  YAYY

Thanks all!  :Smilie:

----------

