Dealing with names...

  • Thread starter Thread starter Jason Puckett
  • Start date Start date
J

Jason Puckett

Field names used are:

strFirstName, strMiddleName, strLastName





Example 1: strFirstName=John | strMiddleName=Null | strLastName=Long

Example 2: strFirstName=B. | strMiddleName=A. | strLastName=Smith

Example 3: strFirstName=O. | strMiddleName=Bill | strLastName=Wilson

Example 4: strFirstName=Sam | strMiddleName=A. | strLastName=Johnson



As they are entered above, I can get them to appear as follows using a
fairly simple concatenation such as Name: [strFirstName]&("
"+[strMiddleName])&" "&[strLastName]



John Long

B.A. Smith

O. Bill Wilson

Sam A. Johnson



My goal is to have the preceding examples appear as follows:



John Long

B.A. Smith

Bill Wilson

Sam Johnson



Essentially, leaving out strMiddleName if null OR it contains only an
initial AND strFirstName contains more than an initial, leaving out
strFirstName if it contains only an initial AND strMiddleName is more than
just an initial, using strFirstName and strMiddleName if they both contain
only an initial.



It didn't seem like it was going to be so complex when I first thought about
it but like so many other issues like this, it's become a bear all of a
sudden. I'm trying to accomplish this in a query but if it isn't possible I'd
be open to any other suggestions. Any assistance would be greatly
appreciated.





TIA



JP
 
JP,

Well, you could write a user-defined function, but it's probably just as
easy in a query expression. I think this will work...

FullName: IIf(Len([strFirstName])=1 Or
InStr([strFirstName],"."),IIf(Len([strMiddleName])=1 Or
InStr([strMiddleName],".") Or [strMiddleName] Is Null,[strFirstName] &
""+[strMiddleName],[strMiddleName]),[strFirstName]) & " " & [strLastName]
 
Steve,

That is Fantastic!! It worked perfectly. You know, I've been going over
your syntax and it would be great if you had time at some point to just make
a quick reply with a little breakdown in explanation of your steps. You've
skipped a tremendous number of steps that I would have sworn were necessary
with this.

Anyway, besides being the perfect solution, your reply was really quick so
either you are a MASTER or I was just making something very simple way too
complex. I'm going to say it's the former. You are "The Man"!

Thanks Again-

JP


Steve Schapel said:
JP,

Well, you could write a user-defined function, but it's probably just as
easy in a query expression. I think this will work...

FullName: IIf(Len([strFirstName])=1 Or
InStr([strFirstName],"."),IIf(Len([strMiddleName])=1 Or
InStr([strMiddleName],".") Or [strMiddleName] Is Null,[strFirstName] &
""+[strMiddleName],[strMiddleName]),[strFirstName]) & " " & [strLastName]

--
Steve Schapel, Microsoft Access MVP


Jason said:
Field names used are:

strFirstName, strMiddleName, strLastName





Example 1: strFirstName=John | strMiddleName=Null | strLastName=Long

Example 2: strFirstName=B. | strMiddleName=A. | strLastName=Smith

Example 3: strFirstName=O. | strMiddleName=Bill | strLastName=Wilson

Example 4: strFirstName=Sam | strMiddleName=A. | strLastName=Johnson



As they are entered above, I can get them to appear as follows using a
fairly simple concatenation such as Name: [strFirstName]&("
"+[strMiddleName])&" "&[strLastName]



John Long

B.A. Smith

O. Bill Wilson

Sam A. Johnson



My goal is to have the preceding examples appear as follows:



John Long

B.A. Smith

Bill Wilson

Sam Johnson



Essentially, leaving out strMiddleName if null OR it contains only an
initial AND strFirstName contains more than an initial, leaving out
strFirstName if it contains only an initial AND strMiddleName is more
than just an initial, using strFirstName and strMiddleName if they both
contain only an initial.



It didn't seem like it was going to be so complex when I first thought
about it but like so many other issues like this, it's become a bear all
of a sudden. I'm trying to accomplish this in a query but if it isn't
possible I'd be open to any other suggestions. Any assistance would be
greatly appreciated.





TIA



JP
Click to expand...
Click to expand...
 
Jason,

Translation of the expression into English...

Check the FirstName to see whether it is an initial only.
If it is...
Check the MiddleName to see whether it is an initial only, or null.
If it is....
Return the FirstName and MiddleName
Otherwise (i.e. if the MiddleName is an actual name)....
Return the MiddleName
Otherwise (i.e. if the FirstName is an actual name)....
Return the FirstName
Then tack the LastName on to the above.

Make sense?
 
Perfect! Thanks again Steve

JP


Steve Schapel said:
Jason,

Translation of the expression into English...

Check the FirstName to see whether it is an initial only.
If it is...
Check the MiddleName to see whether it is an initial only, or null.
If it is....
Return the FirstName and MiddleName
Otherwise (i.e. if the MiddleName is an actual name)....
Return the MiddleName
Otherwise (i.e. if the FirstName is an actual name)....
Return the FirstName
Then tack the LastName on to the above.

Make sense?
Click to expand...
 
Steve-

I've got one more for you...How could I accomodate a name like Sue Ellen
Jones if the individual goes by Sue Ellen but Sue is entered in the
strFirstName field and Ellen is entered in the strMiddleName field?
Essentially, I'd want the query to return strFirstName&" "&strMiddleName&"
"&strLastName in cases where strFirstName AND strMiddleName were neither
null nor 1 character nor contain a "." . That in addition to the former
conditions.

TIA-

JP
 
Jason,

Probably the most "correct" approach here would be for "Sue Ellen" to be
the entry in the strFirstName field. Anyway, names are often tricky in
databases, because there are always exceptions to the general pattern,
especially when you start to get the international factor into it.
Anyway, try this, which is just a simple extension to what we had before....

FullName: IIf(Len([strFirstName])=1 Or
InStr([strFirstName],"."),IIf(Len([strMiddleName])=1 Or
InStr([strMiddleName],".") Or [strMiddleName] Is Null,[strFirstName] &
""+[strMiddleName],[strMiddleName]),[strFirstName] &
IIf(Len([strMiddleName])=1 Or InStr([strMiddleName],".") Or
[strMiddleName] Is Null,""," " & [strMiddleName)) & " " & [strLastName]
 
Steve, once again you've been a world of help. Thank you so much for taking
the time to give me a hand here.

JP


Steve Schapel said:
Jason,

Probably the most "correct" approach here would be for "Sue Ellen" to be
the entry in the strFirstName field. Anyway, names are often tricky in
databases, because there are always exceptions to the general pattern,
especially when you start to get the international factor into it. Anyway,
try this, which is just a simple extension to what we had before....

FullName: IIf(Len([strFirstName])=1 Or
InStr([strFirstName],"."),IIf(Len([strMiddleName])=1 Or
InStr([strMiddleName],".") Or [strMiddleName] Is Null,[strFirstName] &
""+[strMiddleName],[strMiddleName]),[strFirstName] &
IIf(Len([strMiddleName])=1 Or InStr([strMiddleName],".") Or
[strMiddleName] Is Null,""," " & [strMiddleName)) & " " & [strLastName]

--
Steve Schapel, Microsoft Access MVP


Jason said:
Steve-

I've got one more for you...How could I accomodate a name like Sue Ellen
Jones if the individual goes by Sue Ellen but Sue is entered in the
strFirstName field and Ellen is entered in the strMiddleName field?
Essentially, I'd want the query to return strFirstName&"
"&strMiddleName&" "&strLastName in cases where strFirstName AND
strMiddleName were neither null nor 1 character nor contain a "." .
That in addition to the former conditions.

TIA-

JP
Click to expand...
Click to expand...
 
Back
Top