Query to Delimit a column of data

[Ozzie via AccessMonster.com]

I am having a problem replicating a function from excel in access. My
problem is this,

I have a column of data that needs to be delimited, ie in excel I would do;
Data\Text to Columns\Delimited\SemiColon\Finish, however how do you do this
in access????? The data comes directly from the system this way and cannot
get around it.

I have tried an Array formula but with no joy,

If anybody has any ideas they would be really welcome,

many thanks

David

 

[George Nicholson]

If its a single column of data in a table, maybe you can export that table
as a delimited text file and then re-import it. My thinking is that the Text
Import wizard handles the parsing of delimited values fairly well (much like
Text-To-Columns in Excel) and your easiest solution would be to leverage
that functionality, if possible. ("...comes directly from the system this
way ..." Any chance you can use the Text Import Wizard on the original
data?)

Otherwise, how many values are contained in your single column? 2? 6? 30?

HTH,

 

[Ozzie via AccessMonster.com]

Morning George,

Thanks for your response below, unfortunately I can't re-import as I am
linking directly to the system via an odbc link.

There are 24 values contained in the column each seperated by a semicolon.

many thanks

David

If its a single column of data in a table, maybe you can export that table
as a delimited text file and then re-import it. My thinking is that the Text
Import wizard handles the parsing of delimited values fairly well (much like
Text-To-Columns in Excel) and your easiest solution would be to leverage
that functionality, if possible. ("...comes directly from the system this
way ..." Any chance you can use the Text Import Wizard on the original
data?)

Otherwise, how many values are contained in your single column? 2? 6? 30?

HTH,

I am having a problem replicating a function from excel in access. My
problem is this,

[quoted text clipped - 14 lines]

David

 

[John Nurick]

If you search at http://groups.google.com for my name and "safesplit"
you'll find a little VBA function you can call in an Access query to
return one value at a time from your ;-separated string.

Morning George,

Thanks for your response below, unfortunately I can't re-import as I am
linking directly to the system via an odbc link.

There are 24 values contained in the column each seperated by a semicolon.

many thanks

David

If its a single column of data in a table, maybe you can export that table
as a delimited text file and then re-import it. My thinking is that the Text
Import wizard handles the parsing of delimited values fairly well (much like
Text-To-Columns in Excel) and your easiest solution would be to leverage
that functionality, if possible. ("...comes directly from the system this
way ..." Any chance you can use the Text Import Wizard on the original
data?)

Otherwise, how many values are contained in your single column? 2? 6? 30?

HTH,

I am having a problem replicating a function from excel in access. My
problem is this,

[quoted text clipped - 14 lines]

David

 

[Ozzie via AccessMonster.com]

John,

many thanks for your response, I followed your link and can see the way its
working however I'm having a bit of a blonde moment!

The link said to put the code into an Append query, but how? I'm not sure
how to apply the code?

Cheers John,

David

If you search at http://groups.google.com for my name and "safesplit"
you'll find a little VBA function you can call in an Access query to
return one value at a time from your ;-separated string.

Morning George,

[quoted text clipped - 23 lines]

 

[John Nurick]

In your database create a new module (standard module, not a class
module). Name it something like vbaFunctions. If
Option Explicit
doesn't appear at the top, type it in. Then paste the SafeSplit() code
into the module.

You can then use SafeSplit in a calculated field in query as if it were
a standard VBA function. For instance, if you have a field named XXX
that contains ;-separated values like this
cat;dog;goldfish
red;green;blue;yellow;avocado
you can type this into the "Field:" row of the query design grid to
return the first item in each list (cat, red):

Item1: SafeSplit([XXX], ";", 0)

while this will return the second item (dog, green):

Item2: SafeSplit([XXX], ";", 1)

John,

many thanks for your response, I followed your link and can see the way its
working however I'm having a bit of a blonde moment!

The link said to put the code into an Append query, but how? I'm not sure
how to apply the code?

Cheers John,

David

If you search at http://groups.google.com for my name and "safesplit"
you'll find a little VBA function you can call in an Access query to
return one value at a time from your ;-separated string.

Morning George,

[quoted text clipped - 23 lines]

David

 

[Ozzie via AccessMonster.com]

John,

Cheers for the response. its works spot on,

many thanks

In your database create a new module (standard module, not a class
module). Name it something like vbaFunctions. If
Option Explicit
doesn't appear at the top, type it in. Then paste the SafeSplit() code
into the module.

You can then use SafeSplit in a calculated field in query as if it were
a standard VBA function. For instance, if you have a field named XXX
that contains ;-separated values like this
cat;dog;goldfish
red;green;blue;yellow;avocado
you can type this into the "Field:" row of the query design grid to
return the first item in each list (cat, red):

Item1: SafeSplit([XXX], ";", 0)

while this will return the second item (dog, green):

Item2: SafeSplit([XXX], ";", 1)

John,

[quoted text clipped - 22 lines]