work through and replace numbers with txt on comma delimited fields

stankraj

New member
Local time
Today, 12:52
Joined
Sep 2, 2009
Messages
5
Hi All,

I'm new to this board and have a question about a function that might be easier in VB than straight ms access query writing.

The problem I am having is that I have a field that contains numbers separated by commas, these are coded fields which relate to another table with the decoded txt for each #.

What I need to be able to do is run an update on a table to insert the decoded text where the corresponding number is.

Example: Table1 coded field name is CodedField, Table2 decoded txt field is DecodedTxt and another field for CodeNumber to relate to CodedField

Code:
Codes  1=x, 3=y, 5=O (for example!)
 
CodedField = 3,5,1
 
Should in turn read: y, O, x

I've tried using the Replace() func along with Dlookup, but its not working.

Any help is appreciated. Like i said, VB is fine if I can just call a function in an update query or if its easier in sql/query writer that'd be great too.

Thanks

stan
 
Hi,

This should work:

Code:
Public Function Decode(strCodedField As String) As String
    Dim strCode() As String
    Dim i As Integer
    
    strCode = Split(strCodedField, ",")
    
    Decode = ""
    
    For i = 0 To UBound(strCode)
        If Decode <> "" Then
            Decode = Decode & ", "
        End If
        Decode = Decode & DLookup("DecodedTxt", "Table2", "CodeNumber = " & _
                                    Trim(strCode(i)))
    Next
    
End Function

Add this to a module and call the function in your query. That will give you a base to start from. You can then modify it to specify the table to look into, etc.

Simon B.
 
is it possible to add a 2nd condition onto this?

I need Decodes table to only pull codename=MV1 codenumbers back.. the problem with decodes table is it has MV1 through MV13 (13 sets) of codes, each with different meanings. Right now, if I run what you put together.. for some very odd reason, for any instance of 1 as the codenumber, it returns MV13s code.. i need to restrict this to mv1 only.. so I'd probably copy this script for each decode type (13x copies?) in order to encompass every little thing I can hit on.

Or perhaps im going about this all wrong?
 
Hi,

Do you mean that right now you have 13 decode text with ID 1 (for example)? If so the lookup will get you the first ID it finds and god knows in which order it searches...

But yes it would be possible to have many conditions, perhaps you could include the MVx in your first table and then lookup for ID y from set MVx..

Simon B.
 
yes that is what I mean.. its odd though.. for 2-5 it pulls the MV1 data, but for 1 it pulls MV13.. its very strange that only 1 go around would pull different data than the rest

unfortunately adding another column wouldn't work as I am trying to decode multiple fields eventually, just wanted to start with 1.. each column is a different decode set, the column i stated here is dealing only with MV1.. maybe I should just make up 13 tables for each decode type.. a headache in itself, but might be simpler so i can re-write the decode to look to each table instead of a master table

stan
 
Hi,

In that case modify the function to pass the set as a 2nd argument and use it in the DLookup criteria.

Something like:
DLookup("DecodedTxt", "Table2", "CodeNumber = " & Trim(strCode(i)) & " AND decode_set = ' & strDecodeSet & "'")

You would then call the function like this in you SQL :
... Decode(CodedField1, "MV1") AS field_x, Decode(CodedField2, "MV2") AS field_y ...
 
Is this external data that has been presented to you in this format, or do you have design control over the structure?

If the latter, then you have a normalization problem - multiple values shouldn't be stored in a single field - if there's a need to store an indefinite number of attached values per record, they should be in a child table linked back to the main table records by a many-to-one relationship.

Storing each distinct value as a separate row like that means they can be translated from one coding convention to another by means of a simple query against a table containing pairs of original/translated codes.
 
I get this information automatically sent to me in this format. The actual oracle tables that hold this data are normalized, but they concat the multi value fields into a single column.. its a nightmare to decode since some codes are letters some are number.. just more annoying that the replace functions don't like to look for what I tell them to look at!
 
not sure why, but its giving me an error when trying to close out the edited statement now..


Code:
Decode = Decode & DLookup("[Decodes].[CodeName]", "DataDump", "CodeNumber = " & Trim(strCode(i)) & _
        " AND [Decodes].[CodeName] = ' & strDecodeSet & "'")


gives me compile error: expected: list separator or ) and highlights the apostrophe between the quotes? obviously missing a quote mark, but no matter where I insert it, the function fails to run
 
Hi,

Correction in RED:

Decode = Decode & DLookup("[Decodes].[CodeName]", "DataDump", "CodeNumber = " & Trim(strCode(i)) & _
" AND [Decodes].[CodeName] = '" & strDecodeSet & "'")

However, the DLookup doesn't look good to me... In pseudo-code:

DLookup(decoded_text_field, decoded_table, decoded_table_ID = strCode(i) AND decoded_table_set = strDecodeSet)

HTH,

Simon B.
 
Simon hit it. You don't use [Decodes].[CodeName] as the field. It would simply be [CodeName] and the table has to include that field (or you can use a query which can have more than one table, but you still only reference the field and then the table). So DataDump would need to have [CodeName] in it, for your code to work.
 
I wrote a little function called MultiReplace - here it is:

Code:
Public Function MultiReplace(InputString, ReplaceValues, WithValues) As String
Dim intLoop, intPos As Integer

If IsNull(InputString) Then Exit Function

For intLoop = 1 To Len(InputString)
    intPos = InStr(ReplaceValues, Mid(InputString, intLoop, 1))
    If intPos > 0 Then
        MultiReplace = MultiReplace & Mid(WithValues, intPos, 1)
    Else
        MultiReplace = MultiReplace & Mid(InputString, intLoop, 1)
    End If
Next intLoop

End Function

Usage: MultiReplace([yourfieldname],"[characters you want to replace]","[substitute characters]")

So if you have [CodedField] containing:
3,5,1
6,1,2,5,3
3,3,2

and you do: MultiReplace([CodedField],"123","ABC") - you end up with:
C,5,A
6,A,B,5,C
C,C,2

It works with any kind of string input, so if you have [YourStringField] containing:
Hello
World

and you do: MultiReplace([YourStringField],"elo","310") - you end up with:
H3110
W0r1d

You just need to make sure that the two string you pass containing the characters to be replaced and their substitutes are the same length and contain the characters in corresponding order.
 

Users who are viewing this thread

Back
Top Bottom