The opposite of concatenate?

[nowanda]

Hello All!

What is the opposite of concatenate when you are dealing with a table in Access?

I have a table in Access 2000 with the following field:

Detail
31.1;Steve;
29;Mike;
59.6;Rick;
32;Derek;

And I need to split it into two fields:
Percent
31.1
29
59.6
32

Name
Steve
Mike
Rick
Derek

Note that ";" is a delimiter. I can think of a few ways in Excel - but they don't freely translate into Access...

Is there a built-in function I can work with? Is there another way other than a giant nested If which would search for the ";" and then cut the characters before that number and paste elsewhere?

Any tips?

Nowanda

 

[Russell Hauf]

Actually, the functions in Excel for this DO translate to Access!

Try this in your query:

Pct: Left([Detail], InStr([Detail],";")-1)

Name: Mid([Detail],InStr([Detail],";")+1,Len([Detail])-InStr([Detail],";")-1)

HTH,

Russell

Note: This assumes that there is a semicolon at the end like in your example. It also assumes no spaces after the last semicolon. If there are spaces after the last semicolon, replace each instance of [Detail] with Trim([Detail]).

 

[nowanda]

Aha!!

Of course I had my excel figuring more complicated using SEARCH - but your solution rocks!!

Thanks for the help!

Nowanda

 

[jcurran516]

Sorry I was in Excel brain modes and am not a big access guy BUT, in excel under Tools or Data you will see a Text to Columns tool. You can pick the delimiter and just make sure you have enough empty columns to the right for each split field.