Expression question. Switch a letter for a word

P

pgarcia

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

Thanks
 
J

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
 
K

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.
 
L

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
 
Top