Convert Excel function to Access Query

[Rod]

I have some raw Excel data in the format:
2170696969
2021965
2023241
2241710101
2018501
2050183

The goal is to past this data from a PDF file into an Access table.

The data needs to end up taking the area code from the previous cell (row 1
being the exception) and create the second column:
2170696969 2170696969
2021965 2172021965
2023241 2172023241
2225691 2172225691
2241710101 2241710101
2018501 2242018501
2050183 2242050183
2052903 2242052903

I have an Excel formula to convert raw data into a specific format.
Cell B1: =CONCATENATE(IF(LEN($A1)=10,LEFT($A1,3),LEFT(A1,3)),RIGHT($A1,7))
Cell B2..B65536:
=CONCATENATE(IF(LEN($A2)=10,LEFT($A2,3),LEFT(B1,3)),RIGHT($A2,7))
The row designations increments, so row 3 looks like this:
=CONCATENATE(IF(LEN($A3)=10,LEFT($A3,3),LEFT(B2,3)),RIGHT($A3,7))
.... all the the way down.

Excel does not have enough rows to handle the raw data, thus Access. The
Access table looks like:
RawData FinalNumber

FinalNumber should be of type text and look like:
2172021965

THANKS FOR YOUR HELP!

 

[Duane Hookom]

Access records have no order so you would need to add a column/field that
identifies the order of the records. Once you have done this, my solution
would be to create a small user-defined function that loops through all the
records and performs the update on the records.

If you need help with the code, come back with actual table and field names
including the field that determines the order.

 

[Rod]

Hopefully this helps:
Table: tblDNRawData
Records:
RecOrder of type AutoNumber to be used to order the numbers how you
suggested.
DNData is where I paste the copied data of type text as a max of 14
characters
Number is where I would like to store the 10 "digits" of type text.

I understood what you wrote at a high level, but do not have any idea how to
implement it.

THANKS for your help!

 

[Duane Hookom]

You can create a new module and paste this procedure into it:
'=== code begin ===
Sub FixPrefix()
'this code requires a reference set to the MS DAO object library
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strPrefix As String
Dim strNumber As String
Dim strData As String
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT RecOrder, DNData, Number " & _
"FROM tblDNRawData ORDER BY RecOrder")
With rs
.MoveFirst
Do Until .EOF
strData = .Fields("DNData")
If Len(strData) = 10 Then
strPrefix = Left(strData, 3)
strNumber = strData
Else
strNumber = strPrefix & strData
End If
.Edit
.Fields("Number") = strNumber
.Update
.MoveNext
Loop
.Close
End With
Set rs = Nothing
Set db = Nothing
End Sub
'=== code end ===
Save the module as "modConversions". Open the debug window (press Ctrl+G)
and type in
+-------------
| FixPrefix
|

 

[Rod]

Hi Duane,

I followed the instructions. When I pressed Ctrl-G MS Visual Basic came up
and the cursor was in the "Immediate" window. I typed in FixPrefix, pressed
enter, and received the error:

Run-time error '3163':
The field is too small to accept the amount of data you attemted to add.
Try inserting or pasting less data.

Can you decipher this error? I'm dead in the water with a huge backup of
work dependent on this.
Thanks.

 

[Duane Hookom]

Apparently your "Number" field is too small to accept the value trying to be
entered into it. You should set a break point early in your code so you can
step through it and mouse-over variables to view their values.

 

[Rod]

Fixed the previous error by changing the number to length 14.
I executed the instructions...It did not respond while processing, than came
back with an error stating not enough disk space. I have 118GB free. The
30032 records that it did change did not have the format ########## - the ten
numbers striping " ", "-", "(", and ")". The resulting format was ###-####
except the first number, i.e. it looks like it just copied the DNData data to
Number. Here are the first three rows:
RecOrder DNData Number
1 (217) 069-6969 (217) 069-6969
2 202-1965 202-1965
3 202-3241 202-3241

 

[Duane Hookom]

This is the data I got after running the code:
RecOrder DNData Number
1 2170696969 2170696969
2 2021965 2172021965
3 2023241 2172023241
4 2241710101 2241710101
5 2018501 2242018501
6 2050183 2242050183

I don't recall any previous mention of " ", "-", "(", and ")". What's up
with that?

 

[Rod]

I will use the breakpoints to try to track the break down on my end.

You are exactly right about the cleanup of the data! I was so focused on
getting the area code pre-pended that I just glossed over it - there is no
way you would have been able to read my mind on that - by error. ##########
is the final state I need the numbers in - hope that doesn't cause you too
much trouble to incorporate into your solution, but the original pasted data
is the DNData field.

 

[Duane Hookom]

I still don't understand where the -() are coming from. Are these stored in
your data somewhere? If you want to get rid of them, use the Replace()
function.