Access Report numeric value to text conversion

[KevinCowger]

OK I give. I have spent the majority of a beautiful Saturday trying to get a report to work properly. I have set a group of radio buttons in a form to fill the data table column listed as "Quality". Used the default settings for the radio buttons so that they enter a numeric value into the table. This works perfectly but it aint that complicated so I am not bragging.

Now when I create a report and one of the fields I want to see is based upon the table column labled 'Quality". Still no problem now but all I get is 1,2,3,4 ETC based upon the numeric value the buttons are entering. So I went and spent all day trying to create an expression, opening the report in design view and selecting the Details section and the field area marked Quality, so that when the report looks at the data table and sees a 1 it converts that 1 to the word " Excellent" and thats what I see on the report when I print it out. I am stumped that something this simple isnt working. So if someone could offer some advice I would appreciate it because I am tired of feeling stupid today.

Heres the latest expression I came up with and all it is doing is asking me for Parameter values.

=IIf([Movie table]![Quality]=1,[Excellent],IIf([Movie table]![Quality]=2,[Good],IIf([Movie table]![Quality]=3,[Average],IIf([Movie table]![Quality]=4,[Aint worth watching]))))


Thanks in Advance

 

[MasterPO]

You were SOOOOO close! In your expression, you enclosed the literals that you want to print in brackets instead fo quotes. This causes Access to see them as if they were a data field or input parameter. Try this:

=IIf([Movie table]![Quality]=1,"Excellent",IIf([Movie table]![Quality]=2,"Good",IIf([Movie table]![Quality]=3,"Average",IIf([Movie table]![Quality]=4,"Aint worth watching&quot)))

Personally, I hate the nested IIf statements. I find it really hard to keep track of whats going on! Another approach is to create a function that will return the literal you are seeking:

Public Function GetLiteral(Quality)
Select Case Quality
Case is = 1: GetLiteral = "Excellent"
Case is = 2: GetLiteral = "Good"
Case is = 3: GetLiteral = "Fair"
Case is = 4: GetLiteral = "Ain't worth watching"
Case Else: GetLiteral = "Not Rated"
End Select
End Function

Call the function by setting the control source on the report to:

=GetLiteral(([Movie table]![Quality])

I prefer this approach because it's easier to read, and easier to add additional values if your rating system expands!

Good Luck -

 

[MasterPO]

Oops!
Note: Type in the last message - Use

=GetLiteral([Movie table]![Quality])

for the control source...

 

[KevinCowger]

I really appreciate the advice. But I must have messed something up early on in this project because I have tried both your excellent suggestions and if I use the nested IIF statements I get the parameter request and then all the report says is #ERROR. So then I figured that the public function was as you indicated the better way to go so I opened the report in design view and then selected the Code button and entered the info exactly as you typed it because it looked right. However when I run the report I get a parameter request and then a Type Mismatch error and when I go into the debug mode the first Case line is highlighted and I cant for the life of me figure out what the problem is.
Like I said I appreciate the help but it just aint my day because even with your expert help I am not getting things to work the way it should.............. OH well theres always another day

Kevin

 

[KevinCowger]

I wanted to add some new info. I was searching the internet for some additional angles on this issue and found an article that suggested something to me so I made one small change in your provided function statement, which I intend to use once I get it working, the change now causes a parameter input box to appear and if I put anything into it I get the report to run but all items under Quality are marked as Not Rated. Its an improvement at least

I put the Select Case "Quality" in quotes and that started things going but I am still missing the last piece to the puzzle so HELPPPPPPPP. I wonder if maybe its the data type in the data table?

Kevin


Public Function GetLiteral(Quality)
Select Case "Quality"
Case Is = 1: GetLiteral = "Excellent"
Case Is = 2: GetLiteral = "Good"
Case Is = 3: GetLiteral = "Fair"
Case Is = 4: GetLiteral = "Ain't worth watching"
Case Else: GetLiteral = "Not Rated"
End Select
End Function