0

I'm trying to split data that is delimited by a ';', for example below:

plastic; rubber; paper; wood

However the number of records changes on the cell and all of the examples I've seen on this like below already know how many columns the data is to split into.

How do you split data from one column into two?

And I can't simply split the cell up into columns before I load it into access because this list is just one cell in a larger database.

Is there any way to split the data into the number of columns it needs rather than a predetermined number?

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Exostrike
  • 169
  • 10
  • 2
    You can use SPLIT https://support.microsoft.com/en-ie/kb/266289, it may be slow and require code. – Fionnuala Sep 08 '16 at 10:20
  • Could you please explain a bit more your question? You mean that you have a string of data delimited by ';' in vba macro? And you want to split that string into an Access table? other strings? using SQL or vba? – Blenikos Sep 08 '16 at 10:21
  • @Blenikos sorry, I'll expand. I have the list in an Access table cell which I am trying to break down into columns. I had hoped to use Access inbuilt SQL editor but it doesn't seem to be working. – Exostrike Sep 08 '16 at 10:24
  • You still haven't explained **what you want to do** with the split elements. Write them to a table? The same table or a different one? Then how do you know how many columns this table will need? What are the column names? ... – Andre Sep 08 '16 at 10:49
  • @Andre I'm trying to break down the data and store it somewhere so that a query like this: http://stackoverflow.com/questions/39354160/ms-access-selecting-against-nested-user-input can search through it can provide a name to the products. Thats one of the reasons why I wanted to keep it in SQL so that it can be done as part of the same query. – Exostrike Sep 08 '16 at 10:53
  • 1
    Are you trying to do something like what is described in [this question](http://stackoverflow.com/q/19797377/2144390)? – Gord Thompson Sep 08 '16 at 11:00

1 Answers1

0

There is no split function in Access SQL and you cannot do what you want using only one query.

You will need a combination of vba/macro and SQL to accomplish what you want. However your question is to general right now to be able to help you exactly.

I will recommend that you start by building your macro, read from the table into a string using an SQL query (saved or not) and use the split function mentioned in the comments above. Then the splitted string, you can store it wherever you wish into, using a query or anything else you want.

Your Query should be like: SELECT phrase FROM Table Then you go through the results on the recordset with a loop, use split in each of those results and store the result into an array. Finally store the result of the array into a table using again a SQL query.

split can be used like that:

Split ( expression [,delimiter] [,limit] [,compare] )

for example you can do the following

Split ("one:two:three", ":")
Result: {"one", "two", "three"}

For more info on split you can see here: https://www.techonthenet.com/access/functions/string/split.php

Blenikos
  • 733
  • 10
  • 19