Spliting values ? 2

[Guest_imported]

how can I get the value of one field to go to two fields?

field1 = Smith, John

Need VB code for this one value to be split into these two fields...


field2 = John field3 = Smith

Thanks

 

[jimmythegeek]

First, copy and paste the following function into a new module, and save the module as anything you want.

====================
Public Function GetNamePart(FirstName As Boolean, strName As String) As String
Dim x As Integer

x = InStr(1, strName, ",&quot

If FirstName Then
GetNamePart = Mid(strName, x + 2)
Else
GetNamePart = Left(strName, x - 1)
End If
End Function
====================

Then use the following call:

Field2 = GetNamePart(True, [Field1])
Field3 = GetNamePart(False, [Field1])

The first argument is a boolean, True for first name, False for last name. The second argument is the actual string you want to parse, in this case, [Field1].
[sig]<p>Jim Lunde<br><a href=mailto:compugeeks@hotmail.com>compugeeks@hotmail.com</a><br><a href=

http://www.cgeek.com>CompuGEEKS</a><br>Custom

Application Development[/sig]

 

[THoey]

If you want to do the whole table at one time, create a function called ParseNames(), and create a macro to run it.

Public Function ParseNames()
    Dim db As Database
    Dim rst As Recordset
    Dim WholeName As String
    Dim FName As String
    Dim LName As String
    Dim pos As Integer
        
    Set db = CurrentDb
    Set rst = db.OpenRecordset(&quot;select * from tblEmployees&quot;)   <--- Your Table Name Here
    
    rst.MoveFirst
   
    Do While Not rst.EOF
    
        rst.Edit
        WholeName = rst!field1
        
        pos = InStr(1, WholeName, &quot;,&quot;)
        
        LName = Left(WholeName, pos - 1)
        FName = Mid(WholeName, pos + 2)
        
        rst!Field2 = LName
        rst!Field3 = FName
        
        rst.Update
        rst.MoveNext
       
    Loop
    
    rst.Close
    db.Close
    
End Function

I did steal Jim's code and modified it to suit my needs.

Hope it helps...
[sig]<p>Terry M. Hoey<br><a href=mailto:th3856@txmail.sbc.com>th3856@txmail.sbc.com</a><br><a href= > </a><br>Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?[/sig]

 

[maverick]

Thanks Guys,
I'll check both out and post the outcome...

[sig][/sig]

 

[MichaelRed]

Jim, Terry,

Why all this? Cann't it be done w/ a simple update query?

[tab][tab][tab][tab][Field2]

Update To:

[tab][tab][tab][tab]Left(([field1, Len([Field1]) - InStr([Field1], &quot;,&quot - 1)

????? [sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]

 

[maverick]

I think your on to something, Michael

but i only get four charactors back in each feild2

can you fix ?

thanks guys....all [sig][/sig]

 

[maverick]

Terry M.H.,

Thank you very much,
with a little tweak I made your code work perfectly, and I know you could have made it perfect but you didn't have all the details..thats my fault..

Here is the code that works every time after 12tests at 20,200 and 1200 records...

Public Function SplitNames()
Dim db As Database
Dim rst As Recordset
Dim WholeName As String
Dim FName As String
Dim LName As String
Dim pos As Integer

Set db = CurrentDb
Set rst = db.OpenRecordset(&quot;select * from Contacts&quot '<--- Replace Contacts with Your Table Name Here

rst.MoveFirst

Do While Not rst.EOF

rst.Edit
WholeName = rst!FullName '<---replace FullName with the field Name that has the names you want to split in two

pos = InStr(1, WholeName, &quot;,&quot

LName = Left(WholeName, pos - 1)
FName = Mid(WholeName, pos + 2)

rst!LastName = LName '<---replace LastName with the field you want the second name to go to
rst!FirstName = FName '<---replace FirstName with the field you want the first name to go to

rst.Update
rst.MoveNext

Loop

rst.Close
db.Close

End Function

Thanks again to all who had input...

Maverick [sig][/sig]

 

[Husein]

Use Split function which creates a one-dimensional array [sig][/sig]

 

[maverick]

explain ??????? [sig][/sig]

 

[MichaelRed]

Mavcerick,

I think he is refering to a function in VB which is not in Ms. Access (at least as of the '97 ver.) It takes the args of a [sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]

 

[Guest_imported]

Thanks Mav....
for researching that for me...
and your solution...

Lori

 

[MichaelRed]

Mavcerick,

I think he is refering to a function in VB which is not in Ms. Access (at least as of the '97 ver.) It takes the args of a &quot;delimited&quot; string and an optional arg of the delimiter (default is space(1)). Places the string parts (words?) which are in the string into a user defined (supplied) array of strings.

e.g.

MySentance = &quot;This is a list of words to be &quot;Split&quot; into the array&quot;

WdAray() = Split(MySentance)

WdAray(0) = This
WdAray(1) = is
WdAray(2) = list
.
.
.
WdAray(11) = Array


[sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]

 

[Guest_imported]

does it work in access ???

 

[MichaelRed]

Not as far as I know. It is NOT found in the help for Ms Access 97. I have &quot;~~sort of~~&quot; reproduced it '[blue]for amusement purposes only'[/blue]

If you wnat to do some real testing to see that it suits your purposes, just give credit where credit is due


Public Function basSplit(strIn As String, _
Optional DelimChar As String = &quot; &quot _
As Variant

'just give credit where credit is due


'to return an array of the tokens (Words) in a dellimited list of values
'the delimiter may be set by the user. The default value for the dilimiter
'is a single space. The Delimiter may be set to any string, however only the
'first character of the string is used.

'Michael Red, 9/25/00 for the Duvall Group, Columbia, MD
'Usage & Example

'MyArray = basSplit(&quot;Me, Myself, I, Thee, Thou, Though, Go, This is a also a test&quot;, &quot;,&quot
'For xx = 0 To UBound(MyArray): Print xx, MyArray(xx): Next xx
'0 Me
'1 Myself
'2 I
'3 Thee
'4 Thou
'5 Though
'6 Go
'7 This is a also a test


Dim Idx As Integer
Dim Delim As Integer
Dim PrevDelim As Integer
Dim WdsDone As Boolean

Dim WdAray() As String

DelimChar = Left(DellimChar, 1)

Idx = 0 'Init WdAray Index
PrevDelim = 0 'Start w/ Previous position of Delimiter Before String
ReDim WdAray(Idx) 'Initalize array of Words to single element

While Not WdsDone
Delim = InStr(PrevDelim + 1, strIn, DelimChar)
If (Delim = 0) Then 'Can't find any more dellimiters.
'Must be done. Just add the remainder of the Input to this element of WdAray
WdAray(Idx) = Right(strIn, Len(strIn) - (PrevDelim))
WdsDone = True 'Tell'em were done here
Else
'Somewhere in the midst of all this, we jave found a &quot;Real&quot; word
WdAray(Idx) = Mid(strIn, PrevDelim + 1, ((Delim - 1) - (PrevDelim - 1)) - 1)
Idx = Idx + 1
ReDim Preserve WdAray(Idx)
PrevDelim = Delim
End If
Wend

basSplit = WdAray

End Function
[sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]

 

[maverick]

Hey guys, just in case anyone is interested
this is additional code change to avoid going over present records,
to replace the line above;
Set rst = db.OpenRecordset(&quot;select * from [Contacts]where[LastName]Is Null&quot

if you have problems with with code add [ ] to table name as above...

Thanks again everyone for working together on this...

Mav... [sig][/sig]

 

[Husein]

I've just tested MS Access (actually Access 2K) and there is a split function. IF it is NOT in 97, I do apologize. But it works just fine in 2000.