Splitting A String... 1

[ChiTownDiva]

I know I've ask this a dozen times, but here goes...

I have some "Student" names:

W.N. NICKOLOFT
JASEF MOORE
BARRY O'BRYAN
A.R. CASTLEBERRY

I want to put the first name in one field and the last name in another.

When I use:

Left([Student],Len([Student])-InStr([Student]," &quot)

in an Update Query--for the "First Name" field I get the following results:

W.N. NICK
JASEF
BARRY O
A.R. CASTLE

There are no extra spaces between the first and last name. What am I not doing?

Thanks in advance.

 

[jebry]

Hi!

Do it like this:

FirstName = Left([Student], InStr([Student]," &quot - 1)

LastName = Mid([Student], InStr([Student], " &quot + 1)

hth
Jeff Bridgham
bridgham@purdue.edu

 

[ChiTownDiva]

Thanks Jeff...

It worked.

Of course it would--you're at my alma mater---class of '92!

Go Boilermakers!!!

Thanks again...

ChiTownDivaus

 

[jebry]

Hi!

Always glad to help! But it seems better when it is for a fellow Boilermaker!

Jeff Bridgham
bridgham@purdue.edu

 

[ChiTownDiva]

Can I bug you one more time?

I need to capitalize the first letter of the first/last name.

How can I do that? Do I use Instr() and Len().

I'm sorry for being a pain, but I've been working more with Oracle for the last couple of years, so my Access is really rusty. In Oracle I could just use the INITCAP() function.

Thanks.

ChiTownDivaus

 

[mwolf00]

SELECT
uCase (left(student,1),1)
+ right(Left([Student], InStr([Student]," &quot - 1), len(Left([Student], InStr([Student]," &quot - 1) - 1 ), len(Left([Student], InStr([Student]," &quot - 1))) Get the Best Answers! faq333-2924
Is this an asp FAQ? faq333-3048

mikewolf@tst-us.com

 

[jebry]

Hi!

I haven't checked out Mike's response to see if it works. I usually use the StrConv function:

StrConv(Left([Student], InStr([Student]," &quot - 1), vbProperCase)


should capatalize the first letter.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[mwolf00]

Here's the whole thing...

SELECT
uCase(Left(left(student, inStr(student, " &quot - 1),1)) + lCase(mid(left(student, inStr(student, " &quot - 1),len(left(student, inStr(student, " &quot - 1))-1))
, uCase(left(mid(student, inStr(student, " &quot + 1),1)) + lCase(mid(mid(student, inStr(student, " &quot + 1),len(mid(student, inStr(student, " &quot + 1))-1))
FROM myTable


Please note that this will lowercase everything after the first letter (W.N. -> W.n. | O'BRYAN -> O'bryan) which you may not want.... Get the Best Answers! faq333-2924
Is this an asp FAQ? faq333-3048

mikewolf@tst-us.com

 

[ChiTownDiva]

Thanks guys, but when I tried to use either code, I got a compile error...Error 3075

Mike, I used yours like:

SELECT uCase(Left(left( [Lucent Credits].Student, inStr( [Lucent Credits].Student, " &quot - 1),1)) + lCase(mid(left( [Lucent Credits].Student, inStr( [Lucent Credits].Student, " &quot - 1),len(left( [Lucent Credits].Student, inStr( [Lucent Credits].Student, " &quot - 1))-1))
, uCase(left(mid( [Lucent Credits].Student, inStr( [Lucent Credits].Student, " &quot + 1),1)) + lCase(mid(mid( [Lucent Credits].Student, inStr( [Lucent Credits].Student, " &quot + 1),len(mid( [Lucent Credits].Student, inStr( [Lucent Credits].Student, " &quot + 1))-1))
FROM [Lucent Credits];

Jeff I used yours like:

StrConv(Left([Student], InStr([Student]," &quot - 1), vbProperCase)
and

StrConv(Left([Student], InStr([Student]," &quot - 1), vbProperCase())

and I got the same error message.


ChiTownDivaus

 

[jebry]

Hi!

It's possible that StrConv, UCase and LCase are not available in SQL. You can put the conversion in a public function and then call the function from your query.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[mwolf00]

Does this query return any rows?

SELECT * FROM [Lucent Credits} WHERE
inStr(student, &quot; &quot < 1 OR
inStr(student, &quot; &quot IS NULL Get the Best Answers! faq333-2924
Is this an asp FAQ? faq333-3048

mikewolf@tst-us.com

 

[ChiTownDiva]

I used Mikes' function and I'm probably writing this wrong:

Public Function Capitalization() As String

Dim strProperCustomerName As String
Dim Name As String

strProperCustomerName = StrConv(Left([Name], InStr([Name], &quot; &quot - 1), vbProperCase)

Capitalization = strProperCustomerName
End Function

Snot recognizing my function.

 

[jebry]

Hi!

Where did you put the function? It needs to be in a module for it to usable in a query. You can use this:

Public Function Capitalization(varName As Variant) As String

Dim strProperCustomerName As String

If IsNull(varName) = True Then
strProperCustomerName = &quot;&quot;
Else
strProperCustomerName = StrConv(Left([varName], InStr ([varName], &quot; &quot - 1), vbProperCase)
End If

Capitalization = strProperCustomerName

End Function

Then, in the query

FirstName: Capitalization([Student])

You will need to make another function for the last name.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[mwolf00]

Good stuff Jeff - a star for you! Get the Best Answers! faq333-2924
Is this an asp FAQ? faq333-3048

mikewolf@tst-us.com

 

[ChiTownDiva]

I'm sorry Jeff, but I copied your code into my module and running it in an update query.

My Query:

UPDATE [Lucent Credits] SET [Lucent Credits].[First Name1] = Capitalization([Student]);

I get an &quot;Undefined Function 'Capitalization' in expression&quot; error message.

ChiTownDivaus

 

[jebry]

Hi!

Are both fields Student and First Name1 in the Lucent Credits table? When you save the function, it was in a module not a form and the key work Public is still there? Did you name the module Capitalization also? If so, change the name to something else (PublicProcedures maybe).

Can't think of anything else off the top of my head.

hth
Jeff Bridgham
bridgham@purdue.edu