You can also use the Split function (as it works in VB6, included below):
Dim a As Variant
Dim i As Integer
a = Split("ABC DEF GHI", " ")
For i = 0 To UBound(a)
Debug.Print a(i)
Next i
Regards
PerL
'//****************************************************************************
'// Procedure: Function Split () As Variant
'// DateTime: 25.02.2004
'// Author: PerL
'//
'// Purpose: Split a string at positions where 'SplitChar' is found in
'// the string
'//
'// Returns: Variant type string array containing each substring
'//
'//****************************************************************************
Public Function Split(ByVal Expression As Variant, ByVal SplitChar As String) As Variant
Dim a As Variant
Dim pos1 As Long
Dim index As Long
Dim pos2 As Long
Expression = Expression & SplitChar
pos1 = InStr(1, Expression, SplitChar)
If (pos1 > 0) Then
ReDim a(0)
a(0) = Left(Expression, pos1 - 1)
pos2 = pos1 + Len(SplitChar)
pos1 = InStr(pos2, Expression, SplitChar)
While (pos1 > 0)
index = index + 1&
ReDim Preserve a(index)
a(index) = Mid(Expression, pos2, pos1 - pos2)
pos2 = pos1 + Len(SplitChar)
pos1 = InStr(pos2, Expression, SplitChar)
Wend
End If
Split = a
End Function
Rick said:
Jerome wrote:
Hi, I've got the following problem:
I've inherited an old database with a table that contains ONE field
for both the first name and the surname! Now I'd need to separate
them, meaning I want to select the characters until the first space
shows up so I can paste them in a new column and then, in a second
phase, all the characters from the first space onwards to paste those
in yet another column!
What's the best way to do that? And I can I simply do that in a query?
Like using Right([field1];position of first space+1;x)for the surname
and Left([field1];position of first space;x) for the first name. How
can I get the position of the first space?
Thanks a lot!
InStr() function will tell you where the space occurs. Check help for
specifics. You will need to use Mid() for the surname though rather than
Right(). Right() would require a number of positions to grab and InStr() is
going to give a position from the left, not from the right.
FirstName: Left([YourField], InStr(1, [YourField], " ") - 1)
SurName: Mid([YourField], InStr(1, [YourField], " ") + 1)
Of course if you have any "Mary Ann" type first names then this will produce
incorrect results.
Thanks, I'll try that anyway. But you're right for the 'Mary Ann' type
names ... perhaps I should start at the end of the string and then use
the first space that comes. Family names tend to be one name or names
with a hyphen. On the other side you have portugese names like Dos
Santos .... argh, what a DB!?
