Remove characters from a field

[JJPetersen1]

Looking for a way to remove all characters, right of a space, in a field

[madpiet]

Looking for a way to remove all characters, right of a space, in a field

Got an example?

RTRIM() will remove trailing non-printing characters.

Otherwise, you'd need to use INSTR() to get the position of the space, and then maybe LEFT() to get everything up to that. (so maybe subtract 1 from the length.

[JJPetersen1]

AARON B A
SHELBY
SALLY MARY

When I get my listing, I get a listing of names that are inconsistent, some are Last Name, First Name, some are Last Name, Two first names, etc. I have a query to extract the last name ... LName: Mid([FullName],1,InStr([FullName],',')-1). I tried using LName: Mid([FullName],1,InStr([FullName],',')-1) to extract the first name. It's works, unless the person doesn't have a middle name or middle initial (function error). So I tried ... FName1: Mid([FullName],InStrRev([FullName],",")+2). This extracts the examples above. I tried and tried to no avail. Any help would be appreciated

[madpiet]

Oh right. Mailing lists. These are a horror show to fix sometimes, because it's hard to tell programmatically which is a first name and which is a last name. (Like Aaron Neville... if you hadn't heard of him, which is first name, which is last name?) You could, I guess, have a list of common first names, but even then it would not be 100% accurate.

[Welshgasman]

I would save everything as it is presented to you.
You cannot determine whether Sally Mary is first and last or last and first.
Old adage, garbage in garbage out.

[ano]

i not really understand ur function use because u talk about space and u test comma, change comma into space lname should be ok

[madpiet]

I would save everything as it is presented to you.
You cannot determine whether Sally Mary is first and last or last and first.
Old adage, garbage in garbage out.

That was the point of the "Aaron Neville" example. Unless you have outside knowledge, there's no way to tell which is the first name and which is the last name.

There have been discussions about this for YEARS. I'm pretty sure I was discussing this or something very similar with Larry Linson like 10 years ago.

[TheSolver]

Hello Sir.

If database design is donned corretly there could be 3 fields...First name, Middle Name, Last Name.
Maybe now reinput data in correct field struture.

Take care.