Expression question. Switch a letter for a word

[pgarcia]

What would be the expression to change a letter to a word?
O = Other
B= Bill To
P= Prepaid
etc.

Thanks

 

[Jeff Boyce]

Where?

Are you working in a query? If so, one approach might be to use nested
IIF()'s.

But if this is a regular conversion, and if you have a long list (i.e., more
than 3 or 4), why not use a table with these?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[KARL DEWEY]

Use nested IIFs or a translation table.
New_Field_Name: IIF([YourField] = "O", "Other", IIF([YourField] = "B",
"Bill To", IIF([YourField] = "P", "Prepaid", "Unknown")))

Translation_Table --
Letter - text
Word - text

Letter Word
O Other
B Bill To
P Prepaid
Then in your query left join [YourField] to [Translation_Table].[Letter]
field and add [Word] as an alias field.

 

[Larry Daugherty]

Another way is a two step process:

1. Create a new column in the QBE grid as follows:

Converted: =Convert(Coded)

2. Create the function Convert in a standard module:

Public Function Convert(Coded As String) As String
'You add the documentation and error handling...

Select Case Coded
Case "O"
Convert = "Other"

Case "B"
Convert = "Bill To"

Case "P"
Convert = "Prepaid"

Case "etc."
Convert = "et cetera"

Case Else

End Select

Return
End Function

Change the names of things to suit your application. As you can see,
it is easy to add more terms.

The function will be called once for each row returned by the query.

Be sure to use the new field/column "Converted" to show the converted
values.

HTH