How do I use part of a field to lookup a code in another table.

[Guest]

I have a table with a field containing alphabetical characters inserted in
numbers - like 10A6, 7Bde, or 100C1. The alphabetical character is a code for
status. I need to get the character out of the string, interpret what the
character stands for then insert the word back into the string. Like 10A6
should become 10Active6 or 100C1 should be 100Current1. The alphabetic
characters are not always in the same positon. I've set up a table with each
Alphabetic code and the word associated with it. How do I grab interior
section of a string and replace it?

It's sort of like a find and replace, but there must be a better way. I've
got 100,000+ plus records to update. Any help would be appreciated!

 

[Ken Snell [MVP]]

May be easy or may be tricky, depending upon what the letters are that need
to be replaced with long words.

Show examples of original data and how they should look when done. Provide
names of the fields and the tables (including the lookup table). When
posting examples, espeically look for examples where you have more than one
letter in the current data but want to replace just one (or not all) the
letters -- and identify how the program should figure out which letters are
to be replaced.

 

[Guest]

My incoming table looks like this

100A3 Smith John
93B Jones Sam
1C97 Hill George

My lookup table would be:
A Active
B OrderBack
C Third

The finished table should be:
100Active3 Smith John
93OrderBack Jones Sam
1Third97 Hill George

Should I just do Find and Replace? Or is there better (faster) way?

 

[Ken Snell [MVP]]

So a single letter should be replaced with the value from the lookup table?
How many records are in your lookup table?

Also, please tell us the real names of the incoming and lookup tables, and
the real names of the fields within those tables. It'll make it easier to
provide you with code/steps that you can use directly.

 

[Guest]

Yes, a single letter is replaced with a value. There are 6 records in the
lookup table.
Table named "Explanation"
Code Description

A Active
B OrderBack
C Third

Other table named Order

Ordercode LastName FirstName
100A3 Smith John
93B Jones Sam
1C97 Hill George

I didn't design this, just trying to maintain it and simplify the process.

 

[Ken Snell [MVP]]

Create a public function in a regular module.

Public Function ReplaceTheLetters(strOriginal As String) As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strTemp1 As String, strTemp2 As String
strTemp1 = strOriginal
ReplaceTheLetters = strOriginal
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Explanation", dbOpenDynaset, dbReadOnly)
Do While rst.EOF = False
strTemp2 = Replace(strTemp1, rst.Fields("Code").Value, _
rst.Fields("Active").Value, 1, -1, vbTextCompare)
If strTemp2 <> strTemp1 Then
ReplaceTheLetters = strTemp2
Exit Do
End If
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
End Function


Then create an update query, which when run will update your data:

UPDATE [Order]
SET [Order].[Ordercode] = ReplaceTheLetters([Order].[Ordercode]);


Also, do not use Order as the name of a table. It and many other words are
reserved words in ACCESS, and can create serious
confusion for ACCESS and Jet. See these Knowledge Base articles for more
information:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763


--

Ken Snell
<MS ACCESS MVP>