Getting rid of middle initial in query (1 Viewer)

PaulA

Registered User.
Local time
Today, 11:51
Joined
Jul 17, 2001
Messages
416
Hi, all--

I want to remove the space, middle initial and period (".") at the end of values in a name field where the format is "LastName, FirstName M.".

Is there a function that will trim from the right by a determined number of characters? I couldn't find one. With each name having a variable length, the "replace" function doesn't work.

Thanks.
 

Jon K

Registered User.
Local time
Today, 11:51
Joined
May 22, 2002
Messages
2,209
NewField: IIf(InStr(InStr([NameField],",")+2,[NameField]," ")>0, Left([NameField],InStr(InStr([NameField],",")+2,[NameField]," ")-1), [NameField])


The IIf(InStr(InStr([NameField],",")+2,[NameField]," ")>0 is for testing the existence of initials in the name field to avoid possible errors.

See query in attached database.
.
 

Attachments

  • Get Rid Of Middle Initial Access 2000.zip
    10.6 KB · Views: 202

PaulA

Registered User.
Local time
Today, 11:51
Joined
Jul 17, 2001
Messages
416
Jon--

Thanks--very much obliged!

Paul
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:51
Joined
Feb 19, 2002
Messages
42,970
According to relational database theory, in order to be in first normal form, every column of a table must be atomic. That means each column contains one and only one piece of information. Your field has up to three. The best course of action is to fix the problem permanently by separating into three columns. It is very easy to concatenate the individual columns if you need to have them in one field as you would for a mailing address whereas it is very difficult, even when you have delimiters (assuming all records actually have correct delimiters).
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 07:51
Joined
Apr 27, 2015
Messages
6,280
I actually stumbled across a surprisingly easy solution to this yesterday, unfortunately the book I was perusing is at work. I will post the answer once I get to my desk. MTF...
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 07:51
Joined
Apr 27, 2015
Messages
6,280
As promised, here is an excerpt from the Wrox P2P MS Access Developer's Reference:

STRING CONCATENATION TECHNIQUES
Sooner or later, you’ll need to join (concatenate) two strings together. The operator for performing concatenation is &. You may be tempted to say “and” when you see this symbol, but it really means “concatenate with.” A classic example is joining First Name with Last Name, like this:
Code:
strFullName = FirstName & `` ˝ & LastName
This results in the first name and last name together in one string, as in “Tom Smith.”
There are times when you may need to concatenate something to a string, but only if the string actually has a value. For example, you may want to include the middle initial in a person’s full name. If you write code like this:
strFullName = FirstName & `` ˝ & MiddleInitial & `` ˝ & LastName
you will have a small problem. People with no middle name (Null in the table) will have two spaces between their first and last names, like this:
Tom Smith

Fortunately, there is another concatenation operator: +. The technical explanation of this operator is “concatenation with Null propagation.” That’s a great phrase to impress your friends with at parties, but an easier explanation is that it concatenates two strings just as the & operator does, but only if both strings have a value. If either one is Null, the result of the whole concatenation operationis Null.
Using the FullName example, the goal is to have only one space separating first and last names if there is no middle initial. Using +, you can tack on the extra space only if the middle name is not
null:
MiddleName + `` ``

The whole thing looks like this:
Code:
strFullName = FirstName & `` ˝ & (MiddleInitial + `` ``) & LastName
As shown, you can use parentheses to ensure that the operations happen in the correct order. In this case, the inner phrase — (MiddleInitial + `` ``) — will evaluate to the middle initial plus a space, or to Null (if there is no middle initial). Then, the rest of the statement will be performed.

Give it a shot!

Edit: I re-read the OP and realize my contribution is NOT what was asked. Apologies, but keep the info anyway...
 
Last edited:

plog

Banishment Pending
Local time
Today, 06:51
Joined
May 11, 2011
Messages
11,611
I re-read the OP and realize my contribution is NOT what was asked. Apologies, but keep the info anyway

Re-re-read the OP and realize that not only that, but you are over a decade too late.
 

isladogs

MVP / VIP
Local time
Today, 11:51
Joined
Jan 14, 2017
Messages
18,186
Can anyone understand what @ausajid was asking as he/she hasn't responded to my question.
 

plog

Banishment Pending
Local time
Today, 06:51
Joined
May 11, 2011
Messages
11,611
My guess is he wants to start a new thread.
 

Users who are viewing this thread

Top Bottom