Changing numbers to words 1

[SA812]

Hi
I have a query that pulls information from a SQL linked table. The information comes through on a report as either a 1, 2, 3, or 4. Is there a was i can get it to come up as Open, Closed, Cancelled, or Unacknoledged. Below is what the equal
1=Open
2=Unacknoledged
3=Canceled
4=Closed
I'm not sure if i should do it in the query or the report. I've tried a IIF statement with no luck. If access made it's condition format like Excels it would be easy.

 

[dhookom]

The simplest expression to use would be:
=Choose([YourUnnamedField],"Open","Unacknoledged","Canceled","Closed")

The better method would be to create a small lookup table with these values. Another alternative would be to create a user-defined function to return a string based on a number.

Personally, I would use a solution where after discovering that I typed "unacknoledged" rather than "unacknowledged", I would only need to change it in one place.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[SA812]

I guess i'm looking for more details. When i run the report it will return about 20 lines of data. Some line items on the report have a 1 for open and others have a 2 for closed. I don't understand how "=Choose" would know what was a 1 and what was a 2 from the query i created.
=Choose([YourUnnamedField],"Open","Unacknoledged","Canceled","Closed")
Should i just use a SQL statement in the text box properties? Doesn't seem the correct way to go about it.

Thanks for your help
SA

 

[dhookom]

My suggestion assumed you would place the expression in the control source of a text box on your report. You would replace "[YourUnnamedField]" with your actual field name which you never provided.

You could also create a new column in your query like:

StatusTitle:Choose([YourUnnamedField],"Open","Unacknoledged","Canceled","Closed")


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Crowley16]

wow, never knew there was such a function dhookom...

I'd have just messed about with complicated IIFs...

nice

SA812 try using F1

--------------------
Procrastinate Now!

 

[SA812]

Ok i think i get it if it's a value of 1 it chooses the first word "Open" if it's a value of 2 it chooses the second word "Unacknoledged"

Is that correct?

 

[dhookom]

SA812,
Yes, that is how the Choose() function works. Crowley16 mentioned nested IIf() functions which I detest. If your values are not consecutive small integers like 1, 2, 3,... then you might want to use the Switch() function.

However keep in mind that this is data and should be maintained in a table, not an expression. I would create a small lookup table which is much more flexible and allows users to maintain data.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[mtntrvlr]

I'm trying to do a simular task and have created this function to accomplish it in my report. this function works great when used in the control source property of a footer section text box. my problem is that it fails and displays #Error, if used in the detail section of the same report.
i call the function by placing: =SpellShortTimeClass([TimeClass])
in the textbox control source.

here is the function.
i'm using access 2002, sp3 all updates applied.


Public Function SpellShortTimeClass(TC As Integer) As String

Dim myString As String

Select Case TC
Case 1
myString = "BD"

Case 2
myString = "OR"

Case 3
myString = "OB"

Case 4
myString = "FR"

End Select

SpellShortTimeClass = myString

End Function

How does calling change between a footer and a detail section?

 

[dhookom]

What is the name of the text box in the detail section. It can't be the name of a field in your report's record source.

I would have used a small lookup table but a single function isn't a bad alternative.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[mtntrvlr]

Thanks dhookom, I changed the name a couple of times, but it still failed. I then tried your earlier suggestion to SA812 of using the choose() function and it worked great in the detail. I wonder what the difference is. That’s probably a job for someone with a wider propeller on their hat than mine

 

[dhookom]

I don't know why the function would behave differently between the detail and any other section. You made sure the name of the control in the detail section was not the same as any field in the record source.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[mtntrvlr]

yes, i even deleted it, let access name it textbox112, and it still failed. probably an "undocumented feature" of some kind.