[Access 2002] InStr problem

[fischerspooner]

Hi,

I'm banging my head against the desk because I can't find a solution for the following simple problem.

Case: There is a column in a table that has FamilyName and FirstName(s) in one field. ie "McCarthy,J ohn Doe Willy". I have to split up Familyname and the 1st FirstName. The FamilyName works perfect, just looking up the comma with InStr and use Left().
Now can someone throw me a bone to help me get the 1st Firstname. "John" according to the example.

This is what I got but I think InStr function doesn't work properly. I thought the 3 parameters were InStr(placetost artlooking, [field],'SearchString' )

SELECT 
Naam_en_voornamen, 
Left([naam_en_voornamen],InStr(1,[naam_en_voornamen],',')-1) AS Naam, 
InStr([naam_en_voornamen],',')+1 AS Start, 
InStr(9,[naam_en_voornamen],'a') AS stop, 
Mid([naam_en_voornamen],[start],[stop]) AS Voornaam
FROM tbl_kankerpreventie;
 

[MMcCarthy]

Try this:

 
SELECT Naam_en_voornamen, 
Left([naam_en_voornamen],InStr(1,[naam_en_voornamen],',')-1) AS Naam, 
Right([naam_en_voornamen],(Len([naam_en_voornamen])-InStr(1,[naam_en_voornamen],',')+1)) AS Voornaam
FROM tbl_kankerpreventie;
 
 

[fischerspooner]

Thanks for the response but that's the same result I get.

This is what I want to achieve:

"Naam_en_voornamen"      | "Naam"     | "Voornaam"
----------------------------------------------------
McCarthy,John Doe Willy  | McCarthy   | John
 

So it has to drop the "Doe Willy". Locating the first "space" between John Doe is not the problem it's getting out the John ;-)

[PEB]

Hi,

try;

SELECT 
Naam_en_voornamen, 
Left([naam_en_voornamen],InStr(1,[naam_en_voornamen],',')-1) AS Naam, 
Mid([naam_en_voornamen],InStr([naam_en_voornamen],',')+1,InStr(1,[naam_en_voornamen]," ")-InStr([naam_en_voornamen],',')-1) AS Voornaam
FROM tbl_kankerpreventie;
 
 

:)

[fischerspooner]

Works like a charm, I owe you :)