Displaying numbers as letters "DJJ" <gemdjj[ at ]writeme.com> 30.09.2006 16:01:13 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

Re: Displaying numbers as letters "Allen Browne" <AllenBrowne[ at ]SeeSig.Invalid> 30.09.2006 16:26:25 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 -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "DJJ" <gemdjj[ at ]writeme.com> wrote in message news:dxwTg.6788$e66.5248[ at ]newssvr13.news.prodigy.com... [Quoted Text] >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?

RE: Displaying numbers as letters TonyT 30.09.2006 17:35:01 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" wrote: [Quoted Text] > 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 > > >

Re: Displaying numbers as letters "DJJ" <gemdjj[ at ]writeme.com> 30.09.2006 17:43:38 Many thanks... DJJ "TonyT" <TonyT[ at ]discussions.microsoft.com> wrote in message news:0A15F4E9-3124-40C8-87C0-7A073CC21C0B[ at ]microsoft.com... [Quoted Text] > 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" wrote: > >> 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 >> >> >>