MS Access Help?

Question

Well I can't find the function "Text to Column" on MS Access. Basically I would like to Split Entries in one field to multiple fields, i.e:

Field1: A, B, C, D, ......
to
Field2: A
Field3: B
Field4: C
Field5: D
....

NOw I know I can do it on Excel and put it back to Access... but I want to split it using an Access query, then relate this query to other tables in my database. I don't know how to use macros, so if you can have it as SQL then that would make my life easier... thanks

Answer 1

It's potentially possible using pure SQL, only if you know in advance the maximum number of fields there will be on any one table. Because then you can use an update query to tokenize Field1 and insert it to the other fields. But you can't combine DDL and DML into a dynamic routine, so it's not possible to, say, add a certain number of columns to a table based on how many tokens there in the data of a particular field, without dynamically generating the SQL query itself (ie. using a VBA, even if it's to write a function like the above answerer did, which can then be called in a SQL query).



And even then, it's going to be a PITA to do, since there's no easy way to tokenize strings using Jet's SQL functions. You'd have to do it, I imagine, by making use of lots of string manipulation with nested InStr and Mid functions.



You'd be so much better off using VBA to do this.

Answer 2

Copy and paste this sub into a new module (double click on new)

Change the name of my table (tblF) to the name of your table

Palce the cursor insinde the sub (anywhere)

Click on the run button (green triangle)

Done.



Sub SplitFields()

   Dim rstX As ADODB.Recordset

   Dim i As Long



   Set rstX = New ADODB.Recordset

   With rstX

      .CursorType = adOpenDynamic

      .ActiveConnection = CurrentProject.Connection

      .LockType = adLockOptimistic

      .Open Source:="SELECT * FROM tblF"



      Do While Not .EOF

         s = Split(rstX(0), ",")

         For i = 0 To UBound(s)

            rstX(i + 1) = s(i)

         Next i

         .MoveNext

      Loop

   End With

   Set rstX = Nothing

End Sub

Answer 3

Access does not have a Text to Column function.

you could write a function

public function f_Split(Fields as string,ipart as integer ) as string

dim iStart as integer

dim iEnd as integer

iptr = 1

iEnd = 1

do while ipart > 1

iStart = iEnd + 1

iEnd = instr(iptr,sFields,",")

ipart = ipart - 1

end while

f_Split = substr(sFields,iStart, iEnd-iStart)

end function



then in your SQL use

select f_split(Field1,1),f_split(field1,2),....etc to break it up

Answer 4

The document is propietary ergo i think of you could in easy terms run it utilizing MS get admission to. you could in spite of the undeniable fact that browse it and edit it with a application reported as "MDB Browser", google that. you additionally can hyperlink to and question the advice presented you have the ideal drivers put in.