Seperate a field value by spaces

  • Thread starter Thread starter hollyylloh
  • Start date Start date
H

hollyylloh

I am looking for a way to take a field value such as "Valley Swim Club
Association" and seperate out the first two words, and drop any additional
words. I want to do this for a list of such names. Any ideas?
 
You should be able to use the Instr() function to find the position of the
spaces (instr returns the position one string inside another... in this case
the string to find is " "), and then the Left() function will return the left
portion of the string, with the length being specified from the Instr
position of the second space.

Instr() returns only the first occurence of the string (your space), but
does allow us to set a position to start it at. So we'll have to call this
one twice... once to get the first space position, + 1, + the second space
position should tell us the correct number for the Left function.




Function GetFirstTwoWords(ByVal strInput As String) As String
On Error Goto Err_Proc

Dim iPos1 As Integer 'First space position
Dim iPos2 As Integer 'Second space position

'Cleanup any accidental double spaces
strInput = Replace(strInput, " ", " ")

'Find the position of the first space
iPos1 = Instr(1, strInput, " ")

'if no spaces were found exit the function
If iPos = 0 Then Goto Exit_Proc

'Find the position of the second space
iPos2 = Instr(iPos1 + 1, strInput, " ")

'Add the two together
iPos2 = iPos1 + iPos2

Exit_Proc:
'Get the left portion of the string
GetFirstTwoWords = Left(strInput, iPos2)
Exit Function
Err_Proc:
Msgbox Err.Num & vbTab & Err.Description
Resume Exit_Proc
End Function


hth

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)
 
I'm sorry, I just realized you'd get an error for invalid use of null if
strInput doesn't have any spaces (iPos2 is null and will fail inside the
Left() function).

You can either set the variable to 0 explicitly in the beginning of the
function, or use the Nz() function inside Left() to handle the null:


Dim iPos1 As Integer 'First space position
Dim iPos2 As Integer 'Second space position
Click to expand...

iPos2 = 0 '<--- Add this
'Cleanup any accidental double spaces
strInput = Replace(strInput, " ", " ")
Click to expand...


OR

change this
GetFirstTwoWords = Left(strInput, iPos2)
Click to expand...

to this
GetFirstTwoWords = Left(strInput, Nz(iPos2, 0))


To actually update the fields, you'd run them through a query or recordset
loop. Let us know if you need a hand with that.

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)
 
On Mon, 15 Jun 2009 18:07:01 -0700, hollyylloh

You can find the first space using the InStr function.
Then can InStr again with the first argument the location of the first
space.
Then you can use the Left function to get the string you wanted.
The help file has details on how to use InStr and Left.

Because of error checking an for cases where there are no two spaces I
would use a VBA function (public function in standard module) and call
it from your query:
select GetFirstTwoWords(myField) from myTable

-Tom.
Microsoft Access MVP
 
Back
Top