Displaying numbers as letters

[DJJ]

I am trying to create a query with a field called CostCode. It is a field
that disguises the cost of a product in a hidden formula based letters
instead of numbers. The formula is as follows:

1 = P

2 = R

3 = E

4 = S

5 = T

6 = O

7 = M

8 = A

9 = C

0 = X

00 = XY

000 = XYZ



Example: $234 = RES



Is there an easy way to do this without writing a massive IF statement and
then trying to concatenate everything?



DJ

 

[Allen Browne]

A Select Case might be better than nested IFs:

Function Decode(CostCode As Variant) As Variant
Dim i As Inteter
Dim strOut As String

If Not IsNull(CostCode) Then
For i = 1 to Len(CostCode)
Select Case Mid(CostCode,i,1)
Case "P"
strOut = strOut & "1"
Case "R"
strOut = strOut & "2"
Case ...

Case "X", "Y", "Z"
strOut = strOut & "0"
Case Else
strOut = vbNullString
Exit For
End Select
Next
End If

If strOut = vbNullString Then
Decode = Null
Else
Decode = CCur(Val(strOut))
End If
End Function

 

[TonyT]

If you create a table with those 2 fields - code and number, then you can
cross reference that to *hide* your value, but that would have to be in vba
at point of display rather than at query level, and would require looping
through the value with a len() coding.

TonyT..

 

[DJJ]

Many thanks...

DJJ

If you create a table with those 2 fields - code and number, then you can
cross reference that to *hide* your value, but that would have to be in
vba
at point of display rather than at query level, and would require looping
through the value with a len() coding.

TonyT..