Select first word from a string. 1

[douggy]

How to I select everything before the first space in a string.

ie if there is a companyname field containing Smiths Joinery Ltd I only want to return Smiths

Regards
Mark

 

[PHV]

SELECT Left(CompanyName,InStr(CompanyName & ' ',' ')-1) AS myName

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[thefourthwall]

Hello PHV,

Reading the access query threads as I've been asked to take over a database for a local charity. 2 weeks ago, knew next to nothing about Access, but its a lot of fun learning.

One of the forms has clients' kids naames, but they're one field for each kid, so that one field has firstname lastname entered. I understand now that this is bad to do, but the charity does not want/need the kids' last names at this time.

Long story short: is it possible to use your above query, modified, to develop two new fields in a new table, one field for 'kidsfirstname', another for 'kidslastname'?

Thanks,
thefourthwall

 

[traingamer]

yes. Technically, you should start a new thread for a new question, but...
You can simply make an update query which set the fields like

UPDATE mytable SET mytable.first = Left(FullName,InStr(FullName & ' ',' ')-1), mytable.last =  Mid([FullName],InStr([FullName]& " "," ")+1)

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[thefourthwall]

Greg,

Thanks for the advice & code.

Question: I assumed that starting a new thread would give the appearance of my not having searched the forum for existing posts first. Was I wrong in that assumption?

Thanks again - star for your post, which really solves the problem!

thefourthwall

 

[traingamer]

Your question was directly related to the original question, but usually a new person starts a new thread. You can always reference the original one, though. My answer should have been appending a null string like PHV's example - not a space. i.e.

UPDATE mytable SET mytable.first = Left(FullName,InStr(FullName & [COLOR=blue][b]""[/b][/color]," ")-1), mytable.last =  Mid([FullName],InStr([FullName]& [COLOR=blue][b]""[/b][/color]," ")+1)

not

...InStr([FullName]& " "...

which probably puts a leading space on the extracted first name.


Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[thefourthwall]

You're right on the code. Thanks again for your help - much appreciated.

thefourthwall