How to use Mid and Right Function in a Query?

[seb]

Hello everybody
I Have a Table with a field (String) having Name1, Name2, Name3 and Name 4. In the field can be any of this:Name1 and Name2 or Name1 and Name2 and Name3 or Name1 and Name2 and Name3 and Name4.
I already separated Name1 in a column using:
Name1:Trim(Left([Name],InStr[Name]," ")))
How can I do to separate Name2, Name3 and Name4 each in a column?
Thank for your help

[pbaldy]

It sounds like there may or may not be more than one name? Only a single name, not first last? You could probably do it with those functions, but it would get complicated, due in part to having to allow for the possibility that they aren't there. I'd probably create a little function using Split() that returned the names. From the sound of it, you might want to pass the function the number 1-4 and have it return the appropriate name, or nothing if there is none.

[seb]

It sounds like there may or may not be more than one name? Only a single name, not first last? You could probably do it with those functions, but it would get complicated, due in part to having to allow for the possibility that they aren't there. I'd probably create a little function using Split() that returned the names. From the sound of it, you might want to pass the function the number 1-4 and have it return the appropriate name, or nothing if there is none.

pbaldy.
Thanks for your reply. I have been checking the help for Split and I couldn't find any information. I don't understand what you mind due I'm just learning sql. However, testing little I found this
Name2: Trim((Mid([NAME],InStr([NAME]," ")+1)))

This Query displays two names. My question is If I can select Name1 in one column and name2 and name3 in another column. How can I select each name per column? I'm so sorry if I do not explain clear since English is not my mother tongue.
Thanks in advance for your help

[pbaldy]

Really?

http://office.microsoft.com/en-us/ac...001228911.aspx

http://support.microsoft.com/kb/266289

You can use your method; you'd use another InStr within the first (to make the second InStr start after the first space), so that for name 3 you're starting after the second space. That said, your method wouldn't work if there was more than two names. If there were three, it would return the second and third, not just the second. Like I said, you can use that method, but it will be complicated.

[seb]

Really?

http://office.microsoft.com/en-us/ac...001228911.aspx

http://support.microsoft.com/kb/266289

You can use your method; you'd use another InStr within the first (to make the second InStr start after the first space), so that for name 3 you're starting after the second space. That said, your method wouldn't work if there was more than two names. If there were three, it would return the second and third, not just the second. Like I said, you can use that method, but it will be complicated.

With the links I have to learn vba.
To solve my problem I use the Make-Table Query with the first query. With the new table I query using right and then left. Now I have the Table as I want. But I was trying to do the steps at a time with sql.
Many Thanks again