Return string using RIGHT function

[Bird Byte]

I have a field where a name was supposed to be entered as Last Name, First
Name. Now I have to separate out the first names (extracting the last names
was no problem). One problem is that some names were entered as last, first
and others were entered last,first - without a space.
The following worked with marginal results:

FName: Right([FullName],InStr([FullName],","))

Some results were fine, while others looked like this:
LName FName FullName
Dummdorfe rfe, Wayne Dummdorfe, Wayne
Nice opher Nice, Christopher
Hassles Charlie Hassles, Charlie

As you can see, some strings returned as wanted, while others didn't.
Any ideas how to get the correct string (entire first name with no comma or
leading space).

Thanks for any help!

 

[Brendan Reynolds]

I have a field where a name was supposed to be entered as Last Name, First
Name. Now I have to separate out the first names (extracting the last
names
was no problem). One problem is that some names were entered as last,
first
and others were entered last,first - without a space.
The following worked with marginal results:

FName: Right([FullName],InStr([FullName],","))

Some results were fine, while others looked like this:
LName FName FullName
Dummdorfe rfe, Wayne Dummdorfe, Wayne
Nice opher Nice, Christopher
Hassles Charlie Hassles, Charlie

As you can see, some strings returned as wanted, while others didn't.
Any ideas how to get the correct string (entire first name with no comma
or
leading space).

Thanks for any help!

The Mid$() function will give you everything after the comma, and then you
can use the Trim() function to strip off any leading space. Here's an
example I tested in the Immediate window ...

? Trim(Mid$("some, text", InStr(1, "some, text", ",") + 1))
text

 

[KARL DEWEY]

Try this --
FName: Right([FullName],Len([FullName])-(InStr([FullName],",")+1))

 

[Douglas J. Steele]

Right wasn't the right function to use.

To get what's after the comma, use

FName: Mid([FullName],InStr([FullName],",") + 2)

or

FName: Trim(Mid([FullName],InStr([FullName],",") + 1))

To get what's before the comma, use

LName: Left([FullName],InStr([FullName],",") - 2)

 

[Bird Byte]

Thanks for the ideas! This approach omitted the first letter when there was
no space after the comma. Brendan Reynolds' solution (using mid and instr
functions then trimming) worked like a charm.
Thanks very much!

Try this --
FName: Right([FullName],Len([FullName])-(InStr([FullName],",")+1))

--
KARL DEWEY
Build a little - Test a little

I have a field where a name was supposed to be entered as Last Name, First
Name. Now I have to separate out the first names (extracting the last names
was no problem). One problem is that some names were entered as last, first
and others were entered last,first - without a space.
The following worked with marginal results:

FName: Right([FullName],InStr([FullName],","))

Some results were fine, while others looked like this:
LName FName FullName
Dummdorfe rfe, Wayne Dummdorfe, Wayne
Nice opher Nice, Christopher
Hassles Charlie Hassles, Charlie

As you can see, some strings returned as wanted, while others didn't.
Any ideas how to get the correct string (entire first name with no comma or
leading space).

Thanks for any help!