Question Update Query to replace letters & digits... (1 Viewer)

[Hanna]

Hi, we have a field of 7m records of varying length, some of which are numbers, some just letters, some alphanumeric, and most which include a dash, space or some sort of punctuation mark.

We need to replace all letters with "L", then replace all digits with "@".

We can replace characters individually using this update query:
Replace([Ref Digit Or Letter],"A","L")

But would like to use wildcard searches to resolve this, something like (these don't work!)...
Replace([Ref Digit Or Letter],"A-Z","L")
Replace([Ref Digit Or Letter],"LIKE [*A-Z*]","L")

Thanks for any help you can provide

 

[jdraw]

?? What exactly is the purpose-- ultimate use?

 

[plog]

Agree with jdraw--this sounds like it could be a band-aid on a bad idea already implemented.

With that said, your best bet is to create a function in a module that converts your string per your rules. You pass it the current value, it returns the correct value:

Function get_ConvertedValue(in_String AS String) As String  
    ' replaces letters in in_String to "L" and numbers to "@"

ret = in_String       ' return value


    ' put logic here

get_ConvertedValue = ret

End Function

You could declare a string of values to look for ("ABC...789") loop through that string once for every character in in_String and replace when you find matches. Or you could even just loop through in_String and see if the current character is a number and convert it to @ and if not convert it to L.

 

[Hanna]

The data that we have relates to part number references & their manufacturers.

The manufacturers generally stick to common formats when they post part numbers.

So if we were able to replace letters with L, and digits with @, then we could see which mfr's are using which formats

EG ABCD-123 would become LLLL-@@@.

Then we could search for which mfr's use that format of reference to carry on investigations.

Thanks

 

[jdraw]

Wow!!
You would not replace the data, you would lose all your info.

What you seem to need -in plain English:

-Identify for each manufacturer, the part number reference formats


I would suggest getting part numbers for a manufacturer.
Process those part numbers to identify the format
Select distinct patterns to get minimum number of formats for that manufacturer.
Then expand to other manufacturers.

But there are probably details not yet mentioned.

 

[Hanna]

Sorry, I should have pointed out that I've copied the references over so that we've kept the originals in a separate field.

We deal in some pretty old, obscure product, and identifying these patterns (in conjunction with the other info on the report) is actually a good jumping off point for us to carry on investigations...

Jdraw it's a band-aid in some ways - and a shot to nothing in lots of others!!!!

Sorry forum newbie here: I've created the module, how do I implement it?!!

(I had been using an update query which I was astounded I managed to pull off!!!)

Thanks again

 

[plog]

You use it like this in a query:

SELECT YourStringFieldName, get_ConvertedValue(YourStringFieldName) AS ConvertedString
FROM YourTableName

Be sure to insert correct field/table/function names.

 

[jdraw]

Hanna,

Beware of UPDATE queries, they can be so unforgiving. Always do a SELECT first to ensure you've got the right records. And always work on a copy. Backup often.

If you want to post a copy of some records and your routine in a database, I'll look at it.

 

[Hanna]

Gooooooooooooooosh I really am on a learning curve here!!!!
Table Name: 99mfroutput
Field Name with the original info: Reference_#
Field where I was going to put the updated references: WIP

Question1 (ahem!) of 2
To create the function, I did create Module:
then should I change the text from the above to this?
"
Function get_ConvertedValue(in_String AS String) As String
' replaces letters in in_Reference_# to "L" and numbers to "@"

ret = in_Reference_# ' return value


' put logic here

get_ConvertedValue = ret

End Function

Question 2(sorry about this!)

To actually run the function, Plog said that I should use it in a query like this...should I change to this:

SELECT Reference #, get_ConvertedValue(YourStringFieldName) AS ConvertedString
FROM 99mfroutput

Many, many, many thanks for reading this far!!!!!

 

[Hanna]

Thanks JDraw, is there enough info in the post above for you to unravel what I'm talking about??!
(Although I'm pretty wet behind the ears when it comes to access, I've been working on Excel for a while. So by hook or by crook, I have been following your advice re: backing info up & making changes a little at a time!!!!).
The last thing I was going to add is that it would be great to be able to see the original info & compare it right next to the Converted Value - hence why I've duplicated the data in the original field to a work in progress type column
Thanks again

 

[jdraw]

What plog was suggesting was to create a query and test your routine.

The routine, a function in this case, takes an input value and converts that value, then outputs the appropriate converted character.

If PartNum is 345-JKL, then your function Hanna123( incoming as string) as string would be used
select manufacturer,partnum, Hanna123(partnum) as ConvertedFormat from yourTableName

Sample code is just for illustration/concept. Real code depends on your naming convention etc.

Do NOT uses embedded spaces or special characters "#" etc in field names..

Here is a function that I think will work.

Function fStrAlphaNumConvert(strInString As String) As String

'Purpose:
'check each character in a string
'convert alpha to L and numerics to @
'do not alter -


'************ Code Start **********

    Dim lngLen As Long, strOut As String
    Dim i As Long, strTmp As String
    Dim Testalpha As String
    Dim TestNumbers As String
    On Error GoTo fStrAlphaNumConvert_Error

    Testalpha = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
    TestNumbers = "0123456789"
    lngLen = Len(strInString)
    strOut = ""
    For i = 1 To lngLen
        strTmp = Left$(strInString, 1)
        strInString = Right$(strInString, lngLen - i)
        If InStr(Testalpha, strTmp) > 0 Then
            strOut = strOut & "L"
        ElseIf InStr(TestNumbers, strTmp) > 0 Then
            strOut = strOut & "@"
        ElseIf strTmp = "-" Then
            strOut = strOut & "-"
        End If
    Next i
    fStrAlphaNumConvert = strOut

    On Error GoTo 0
    Exit Function

fStrAlphaNumConvert_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure fStrAlphaNumConvert of Module AWF_Related"
End Function

And a test routine

Sub teztx()
Dim x As String
x = "12ABC-OPI9"
Debug.Print fStrAlphaNumConvert(x)

End Sub

Result of test: @@LLL-LLL@

Good luck.

 

[Hanna]

Thanks for your help gents, after today's learning curve, I think I'm going to retire to a darkened room with a large whisky!!!

 

[jdraw]

Are you able to follow the code in the function and test routine?

 

[Hanna]

I'll be honest I can't at the moment, but I'm going to do my damnedest to figure this out (bit by bit!).Thanks