access macro

  • Thread starter Thread starter bscholl
  • Start date Start date
B

bscholl

I need to cut data from column 1 and put it in column 2

Example
Column 1 'cut the numbers 267
randal stevens 267

Column 2 ' and insert 267 after the (999)
(999)

i need to do this for 10000 records
 
some of the records in the first colomn look like this

NORTH FAIRMOUNT POOL (921)

I need to input that 921 prefix into the next colomn
after 5 other characters ("areacode") example (513)
i need to imput the 921 after the (513)

(e-mail address removed)
 
This may be a possible solution for you.

First, put the following function in a regular module (name the module
basMod):

Public Function ExtractNumbers(strFieldValue As String) As String
Dim intLoop As Integer
Dim strHold As String, strX As String
strHold = ""
For intLoop = 1 To Len(strFieldValue)
strX = Mid(strFieldValue, intLoop, 1)
If IsNumeric(strX) = True Then _
strHold = strHold & strX
Next intLoop
ExtractNumbers = strHold
End Function


Then create an update query that uses the following "Update To:" expression
for the second column (assuming that the second field is named
NameOfSecondField):
[NameOfSecondField] & ExtractNumbers([NameOfFirstField])

Run the update query.
 
Back
Top