Moving delimited data from one column to another

[kmfdm]

Hello,



How would you create a macro that would move delimited data from 'column1' to 'column2' in a table? Assuming the delimiter is a space.

The only issue is that I want to move everything after the first space to 'column2' - even if there are multiple spaces after the first one.

Is that easy to do?

Thanks...

[pbaldy]

I don't use macros, but in a query or VBA code you could use the Mid() function along with the InStr() function to find the first space.

[kmfdm]

Thanks. Is there an example anywhere of how to do that with VBA in Access?

[pbaldy]

Not that I'm aware of. If you're trying to actually change the data in the table, simplest would be an update query. You can do it in VBA, with the Edit method within a recordset loop.

[June7]

At bottom of http://office.microsoft.com/en-us/ac...001135959.aspx you will find string manipulation functions.

You probably want something like:

Trim(Mid([fieldname], InStr([fieldname], " ")))

The expression can be used on the Update To row of an Access UPDATE query.

In a macro would probably use OpenQuery method to execute the saved Access query object.

In VBA could be:
CurrentDb.Execute "UPDATE tablename SET fieldname = Trim(Mid([fieldname], InStr([fieldname], ' ')))"

If you want the edited data to save to another field and not alter the original data, reference the field names accordingly.

[kmfdm]

Thanks, June7...that SQL actually works well. It doen't seem to work correctly when I change the delimiter, though. using " - " for example:

Trim(Mid([fieldname], InStr([fieldname], " - ")))

It removes the first space and then leaves the "- " in front of the value in the second column. Do I need to change something?

[pbaldy]

InStr() returns the position of the first character, so add a couple:

InStr(...) + 2

[kmfdm]

perfect, thanks for your help. I'll make a macro to run the queries.

[pbaldy]

No problem.