Cut and Paste Text (Data Entry)

[ursino1]

PLEASE HELP WITH:

Cutting and pasting one text string (seperated by spaces) into multiple fields in a table.

WHY:

Excel seperates the cut results of an AS/400 query into seperate columns, depending on the spacing.

Access pastes all the informaton into one field.
How do I stop it from doing that?

Thank you for your help.

 

[FoxProProgrammer]

Hi,

I'm a little confused about the source of the data. Are you trying to import an Excel spreadsheet into Access or is the data already in an Access table? How many spaces are there in each string? If the number of spaces isn't fixed, how many spaces are in the string that has the most spaces? That would determine the number of fields that you need. You can use the Split function for this purpose, and I can give you a better example if you clarify these points.

Thanks, dz
dzaccess@yahoo.com

 

[ursino1]

DZ,
Thank you for your response.
I didn't mean to confuse the question.
No, the new data is not on a spreadsheet. I am trying to get it to seperate the same way it would as if on a spreadsheet. I am not importing I am simply pasting text into fields on a table. Each text string is unique, and requires a cut and paste. I can't do them all at the same time, I have to be able to paste each item one by one, row by row, one row after another. I need it to seperate into the diffrent columns of that one row. Currently it is pasting all the data into the first column of that row.

Thanks again for your help.

 

[FoxProProgrammer]

Thanks for the reply, ursino1. I now understand that you aren't trying to import from Excel, but I still don't understand what the source of the data is or how many spaces are in the data. Please post the structure of your table and sample data. Maybe it will help me understand what you've got. I'm guessing that the data is in a single field in the table, and that you want to split it up into separate fields. To do this, you would have to know how many spaces there are so you would know how many fields to create before you separate the strings. You could create the fields on the fly, but it would complicate the code.

dz
dzaccess@yahoo.com

 

[ursino1]

DZ

I am starting to see that this is not simple matter and I was looking for simple solution.

But for the sake of finding out what do in this case let me just give you the details.

Detail 1: This a preset table with, preset field columns. So seperating the text out from the first field is probably not an option. I am sorry, but I can't provide the field titles.

Detail 2: the number of spaces may vary.

Detail 3: The data is pasted on the 5th field, and should continue until the 9th field.


I can not provide the data but I can give you an example, since the data is nothing more the text.


E.G.:


AB AC AD AE

(this is an example of text data, as it would appear if you pasted it into a text document)


------------------------------------------------------

Column A Column B Column C Column D

AB AC AD AE


(This is how it would appear on Excel document)

--------------------------------------------------------

Field 1 Field 2 Field 3 ...

AB AC AD AE

(How it appears on the access table)


----------------------------------------------------------

Field 1 Field 2 Field 3 Field 4
AB AC AD AE

(how I want it to appear when I paste it)
--------------------------------------------------------



Field 1 Field 2 Field 3 Field 4
Row 1 AB AC AD AE
Row 2 BB BC BD BE


Then next line would be pasted in the same way and so on.

Thanks.

 

[FoxProProgrammer]

Ursino,

In Detail 1, you said that the table is preset. Does that mean that you can't add fields to the table? If you can't add fields to the table, how can you split the data in the first field into separate fields? You confused me with that one.

I understand Detail 2. It's not a big deal, but you will have to know the maximum number of spaces in a string. You would use this information to determine the number of fields that must be created in the table to store the split up data. You can either manually calculate the number of fields required before you split the data or do it on the fly programatically. I'll help you do it either way, but the code would be simpler if you knew ahead of time how many fields are required. For example, if your sample data represented the string in the table with the most number of spaces, you would require four new fields in the table to store the split data.

In Detail 3, you said that the data should start in the fifth field and continue to the ninth field. Is that because fields 2, 3, and 4 contain data that you can't touch?

Here's some code to give you a start. Briefly what it does is open a recordset based on the table that contains the data that you want to split. It then calculates the number of spaces in the string in the record it is operating on and dimensions an array to that number. The code then creates the array with the Split Function so that each array element contains a single value from the space delimeted string.

The Select Case structure is used to store the data in the new fields in the table. I assumed that the new field names were Field1, Field2, etc to Field10. Note that in this example the number of fields is assumed to be 10. In other words, to use this code, you would have predetermined that there are a maximum of 9 spaces in the data that needs to be split, and therefore, 10 fields are needed. If you need more fields you could simply add another Case statement.

Last, the space counter is reset and the record set is moved to the next record. This entire process is inside a While loop that cycles through all the records in the table. There might be a more eloquent way to do this, but I couldn't figure out a way to refer to a field as an expression. For example, you could replace the entire Case structure with a For loop if you could figure out how to refer to a Field as an expression. I couldn't figure it out and I'm pooped out. If anyone else is reading this thread, maybe they know how to do this.

*** This code DOES NOT work ***

For i = 0 To SpaceCount
rs.Edit
rs!Field & Str(i + 1) = strArray(i)
rs.Update
Next i

**** This code DOES work ****

Dim ParseField As String
Dim db As Database
Dim rs As Recordset
Dim SpaceCount As Integer
Dim Position As Integer
Dim strArray() As String

SpaceCount = 0
Position = 0

Set db = CurrentDb
Set rs = db.OpenRecordset("YourTableName", dbOpenDynaset)

While Not rs.EOF

' Count the number of spaces in the string
' Field is the name of the field in the table that contains the unsplit data.
Do While Position < Len(rs!Field)
Position = InStr(Position + 1, rs!Field, &quot; &quot
If Position <> 0 Then
SpaceCount = SpaceCount + 1
Else
Exit Do
End If
Loop

ReDim strArray(SpaceCount) As String

strArray = Split(rs!Field, &quot; &quot

Select Case SpaceCount
Case 0
rs.Edit
rs!Field1 = strArray(0)
rs.Update
Case 1
rs.Edit
rs!Field1 = strArray(0)
rs!Field2 = strArray(1)
rs.Update
Case 2
rs.Edit
rs!Field1 = strArray(0)
rs!Field2 = strArray(1)
rs!Field3 = strArray(2)
rs.Update
Case 3
rs.Edit
rs!Field1 = strArray(0)
rs!Field2 = strArray(1)
rs!Field3 = strArray(2)
rs!Field4 = strArray(3)
rs.Update
Case 4
rs.Edit
rs!Field1 = strArray(0)
rs!Field2 = strArray(1)
rs!Field3 = strArray(2)
rs!Field4 = strArray(3)
rs!Field5 = strArray(4)
rs.Update
Case 5
rs.Edit
rs!Field1 = strArray(0)
rs!Field2 = strArray(1)
rs!Field3 = strArray(2)
rs!Field4 = strArray(3)
rs!Field5 = strArray(4)
rs!Field6 = strArray(5)
rs.Update
Case 6
rs.Edit
rs!Field1 = strArray(0)
rs!Field2 = strArray(1)
rs!Field3 = strArray(2)
rs!Field4 = strArray(3)
rs!Field5 = strArray(4)
rs!Field6 = strArray(5)
rs!Field7 = strArray(6)
rs.Update
Case 7
rs.Edit
rs!Field1 = strArray(0)
rs!Field2 = strArray(1)
rs!Field3 = strArray(2)
rs!Field4 = strArray(3)
rs!Field5 = strArray(4)
rs!Field6 = strArray(5)
rs!Field7 = strArray(6)
rs!Field8 = strArray(7)
rs.Update
Case 8
rs.Edit
rs!Field1 = strArray(0)
rs!Field2 = strArray(1)
rs!Field3 = strArray(2)
rs!Field4 = strArray(3)
rs!Field5 = strArray(4)
rs!Field6 = strArray(5)
rs!Field7 = strArray(6)
rs!Field8 = strArray(7)
rs!Field9 = strArray(8)
rs.Update
Case 9
rs.Edit
rs!Field1 = strArray(0)
rs!Field2 = strArray(1)
rs!Field3 = strArray(2)
rs!Field4 = strArray(3)
rs!Field5 = strArray(4)
rs!Field6 = strArray(5)
rs!Field7 = strArray(6)
rs!Field8 = strArray(7)
rs!Field9 = strArray(8)
rs!Field10 = strArray(9)
rs.Update
End Select

SpaceCount = 0
rs.MoveNext

Wend

I know that this code works because I tested it. It's brute force, but it works. Let me know if you have any questions about how it works and I'll try to help you some more.

Best regards,
dz
dzaccess@yahoo.com