Trim Function????

[shelton1979]

I have a field with the whole name in it with no spaces like Doe,John. I need to have a 2 separate fields in a query: one for the last name and one for the first name. Can anyone help me

 

[jason246day]

See if this will work for you.

call this to get the first name:
splitFields("JohnDoe",1)

call this to get the last name:
splitFields("JohnDoe",2)


'---- Start of code ----
Function splitFields(aString As Variant, part As Integer) As String
Dim i As Long

If Len(aString) < 2 Then Exit Function

For i = 2 To Len(aString)
If Asc(Mid(aString, i, 1)) <= Asc(&quot;Z&quot Then
If part = 1 Then
splitFields = Left(aString, i - 1)
Else
splitFields = Mid(aString, i)
End If
Exit For
End If
Next i
End Function

 

[onpnt]

you could utilize something like

first name = Mid([whole name],inStr([whole name],&quot;,&quot+1)

last name = strReverse(Mid([whole name],inStr([whole name],&quot;,&quot+1))


I'm not completely sure on the support of the functions in older then 2K though


___________________________________________________________________

http://www.onpntwebdesigns.com

The answer to your ??'s may be closer then you think.
Check out Tek-Tips knowledge bank by clicking the FAQ link at the top of the page

 

[onpnt]

something like this

SELECT
Mid(Foo_Me,inStr(Foo_Me,&quot;,&quot+1) AS [first name],
strReverse(Mid(Foo_Me,inStr(Foo_Me,&quot;,&quot+1)) AS [last name]

FROM Foo_Related
WHERE auto = 3;

again, not sure if strReverse is avil in -2K versions.

___________________________________________________________________

http://www.onpntwebdesigns.com

The answer to your ??'s may be closer then you think.
Check out Tek-Tips knowledge bank by clicking the FAQ link at the top of the page

 

[shelton1979]

guys i've tried both and seem to have problems...onpnt...strReverse function doesn't seem to work in 2000 and i'm still working through the code you created jason but i appreciate it. I do know its possible and what the skills i have i should be able to do it. At least i have the first name know so all i have to do is get the last name...

 

[nicsin]

I believe

split

is what you need:

Dim test() as String

test = split("a,b", ",")

msgBox "test(0) = " & test(0) & " and test(1) = " & test(1)

 

[MakeItSo]

Hi there.

nicsin's code should work fine.
If it doesn't:
Here's the left/right version:

Dim inp As String
Dim frst As String, lst As String
inp = Source
lst = Left(inp, InStr(1, inp, ",") - 1)
frst = Right(inp, Len(inp) - Len(lst) - 1)

Where inp should be assigned the full name field, lst and frst will output last/first name separated.

Cheers,
MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de

 

[shelton1979]

okay guys, i've been trying at this far too long. I can get the first name w/ the last name but not by itself....the original looks like this for some text rows:

TYLER,JUSTIN DARNELL

by using the code above, I have been able to get the last name and I have also been also to populate the first name field but when I populate the first name field it would have justin darnell instead of only justin. can you help?

 

[warthog72]

I'd go about it a little differently, find the &quot;,&quot; in the string, everything to the left is the LastName and everything to the right is the FirstName

I would start with MakeItSo and just change how to get the first name.

Dim inp As String
Dim frst As String, lst As String
Dim posComma as long 'find this value first
inp = Source

posComma = InStr(1,inp, &quot;,&quot
lst = Left(inp, posComma - 1)
frst = Mid(inp, posComma + 1)

Where inp should be assigned the full name field, lst and frst will output last/first name separated.

Hope that helps. Worked on my test scenarios.

 

[nicsin]

Shelton,

Dim name as Stirng
Dim test1() as String
Dim test2 as String
Dim flag as Boolean

name = "TYLER,JUSTIN DARNELL"
flag = false

test1 = split(name, ",")

if instr(test1(1)," ") > 0 then
    test2 = split(test1(1)," ")
    flag = true
end if

msgBox "First name = " & test1(0) & " and surname = " & iif(flag,test2(0),test1(1))

It should also wotk for

name = "TYLER,JUSTIN"