Name Seperation

[Ken Morris]

I use ODBC connections to main frame tables (DB2) very
extensively. One of the DB@ tables I use is a fabrication
of an IMS table that I cannot pull through ODBC. The name
in that table is combined in the format [Last, First
Middle]. Is there anyway in Access '97 to seperate those
names so that I can use the [First] name field seperately
from the other two?

Any help is greatly appreciated.
Sincerely
Ken

 

[Ken Morris]

Vanderghast,

Thank you for your help. I have used the following string
of language:

Expr2: Trim(Mid([RNAME],InStr([RNAME],' ')))
First: Trim(Left([Expr2],InStr([Expr2],' ')))
Middle: Trim(Mid([Expr2],InStr([Expr2],' ')))
Last: Trim(Left([RNAME],InStr([RNAME],' ')))

RNAME is in the format: LAST, FIRST MIDDLE

This does what I need it to do, except it is leaving the
comma on the end of the LAST. Do you have any suggestion
as to how to get rid of the comma?

Thanks, again!!
Ken

-----Original Message-----
Hi,

Through ODBC, I can't tell. With VBA (or Access + Jet), I

would use InStr( ) to find the delimiter,

and then, Mid( ), to extract the portion of the string I

need. If those names are required, as

atomic values, then, I would redesign the basic tables:

a field must hold just ONE information.

Hoping it may help,
Vanderghast, Access MVP

I use ODBC connections to main frame tables (DB2) very
extensively. One of the DB@ tables I use is a fabrication
of an IMS table that I cannot pull through ODBC. The name
in that table is combined in the format [Last, First
Middle]. Is there anyway in Access '97 to seperate those
names so that I can use the [First] name field seperately
from the other two?

Any help is greatly appreciated.
Sincerely
Ken

.

 

[Michel Walsh]

Hi,


I would blindly guess

LastWithComma: Trim(Left(RNAME, InStr(RNAME,' ') )) & ", "
LastName: Trim( Left( LastWithComma , InStr( LastWithComma , "," ) ))



Note that I add the character I look for, to be sure I found it (else, InStr may return 0 and 0 -1
, -1 if you prefer, is not friendly to Left( ).


Hoping it may help,
Vanderghast, Access MVP

Vanderghast,

Thank you for your help. I have used the following string
of language:

Expr2: Trim(Mid([RNAME],InStr([RNAME],' ')))
First: Trim(Left([Expr2],InStr([Expr2],' ')))
Middle: Trim(Mid([Expr2],InStr([Expr2],' ')))
Last: Trim(Left([RNAME],InStr([RNAME],' ')))

RNAME is in the format: LAST, FIRST MIDDLE

This does what I need it to do, except it is leaving the
comma on the end of the LAST. Do you have any suggestion
as to how to get rid of the comma?

Thanks, again!!
Ken

-----Original Message-----
Hi,

Through ODBC, I can't tell. With VBA (or Access + Jet), I

would use InStr( ) to find the delimiter,

and then, Mid( ), to extract the portion of the string I

need. If those names are required, as

atomic values, then, I would redesign the basic tables:

a field must hold just ONE information.

Hoping it may help,
Vanderghast, Access MVP

I use ODBC connections to main frame tables (DB2) very
extensively. One of the DB@ tables I use is a fabrication
of an IMS table that I cannot pull through ODBC. The name
in that table is combined in the format [Last, First
Middle]. Is there anyway in Access '97 to seperate those
names so that I can use the [First] name field seperately
from the other two?

Any help is greatly appreciated.
Sincerely
Ken

.