Split Data In One Field Into Two -- With Commas

[databasedonr]

Hi all,

I know this should be a relatively easy solution, but I can't remember/think how, and I can't find a solution by searching....

I have a "legacy" database in Access2000 where a username is entered in a Name field as "Surname, Firstname".

I want to split the data by extracting the surname (to the comma) and writing that into a new field that I have created called Surname.

I know I can do this use Left, Right or Mid functions if my string is the same length, but unfortunately last names are variable.

Is there a nice simple way of updating my data in a query (I'm not great at VB, but could give it a shot with lots of guidance.

Thanks,

 

[FoFa]

The basic code would be:
left([FullName],instr([FullName],",")-1)

Now you can use that in a form, in a query whatever.

 

[databasedonr]

Thanks, FoFa - that worked a treat!

I appreciate it.

 

[databasedonr]

Whoops! That worked great for the lastname, but now if I try it for the first name, I get mixed results.

I have tried using the code:

Right([Fullname], instr([Fullname],",")-2)

on the theory that I also don't want the space in the field: example, "surname, firstname" -- but this does not work for every record -- I get some very weird and mixed results. Ex. I have several fields that work perfectly, and several that give me results like ", firstname" and "ame, firstname" ??

Any ideas what I might be doing wrong?

Thanks again in advance.

 

[FoFa]

Try:
Right([Fullname], len([FullName]) - instr([Fullname],","))

Might have to adjust it +-1

 

[databasedonr]

I'm not quite sure how this works, but...

Per my earlier post, I was successful extracting the surname from my fullname string, but had trouble with the first name, until I tried this:

Mid([fullname],InStr([Fullname],",")+2)

I'm not really sure why this worked, and why the Right function didn't (I even tried InStrRev function without success), but this gives me the results I want/need.

If anyone cares to explain to the forum why this works, I'd be grateful, but in the mean time I will take my results and run.

Thanks again to FoFa for setting me down the correct path

 

[FoFa]

Example Blow, Joe

Instr (for the comma) returns 5
So LEFT("Blow, Joe",5 - 1) return BLOW

Now with RIGHT (in your example) it would be
RIGHT("Blow, Joe",5-2) would return Blo

But MID works on STring, Start, Howmany with Howmany being optional. If How Many is not supplied, it says, until the end.

So Mid("Blow, Joe", 5 + 2, {to the end}) returns Joe

 

[databasedonr]

Thanks again, FoFa,

Exactly right, and I guess our posts crossed, as I hadn't seen your example with Right and the len function.

I did need to adjust that , as follows:

Right([Fullname],Len([FullName])-InStr([Fullname],",")-1)

but it works a treat. Thanks tonnes for the explanation, as it all makes sense now.

Is there any advantage to using Right vs. Mid, as without the end parameter for Mid, it works, too? Does it matter?

Every day I read these forums, I get smarter! Pretty soon, I'll be a wizard!

 

[FoFa]

From what I understand, there is less "overhead" with the LEFT or RIGHT functions, but if I have MID in and working, I don't change to another one on that alone.