Combine 4 fields into 1 with some fields null.

[Full_Williams]

Hi there,

There's a lot of info in the forums on combining fields, but I could find anything to help me out with my specific problem.

I have 4 fields: [FirstName], [LastName], [MidInitial], & [Salutation].

I need to combined these into 1 field for a report so it shows.

ex: Doe, John, J., Ph.D

I can combine with the "&" Operator, but not all the records have [Salutation] or [MidInitial] populated and query results for some of the records are:

ex: Doe, John,,

I need the results without the trailing comma's if the [Salutation] and [MidInitial] fields are null.

So, I've tried using the IIf statement in the query:

Name: IIf(IsNull([MidInitial]),[LName]+", "+[FName],[LName]+", "+[FName]+" "+[MidInitial])

It works fine without using [Salutation], but I can't get it to work with [Salutiation].

Any suggestions on how I can get this all together.

Thanks,
Full Williams

 

[Newman]

Have you tried this?

Name: IIf(IsNull([MidInitial]) and IsNull([Salutation]),[LastName]", "[FirstName], (IIf(IsNull([MidInitial]),[Salutation]", [LastName]", [FirstName], (IIf(IsNull([Salutation]), [MidInitial]", "[LastName]", "[FirstName],[MidInitial]", "[Salutation]", "[LastName]", "[FirstName])))))

Could also use a function that do like "Switch" in C++ or SelectCase in VB, but I don't know the english name of it. ("FirstTrue" in french)

Newman

 

[Full_Williams]

Hi Newman,

Thanks for responding so quickly.

I'm getting "Syntax Error (comma)" when I go to view the query.

In my query field I have:

Name: IIf(IsNull([MidInitial]) And IsNull([Salutation]),[LName]+", "+[FName],(IIf(IsNull([MidInitial]),[Salutation]+", "+[LName]+", "+[FName],(IIf(IsNull([Salutation]),[MidInitial]+", "+[LName]+", "+[FName],[MidInitial]+", "+[Salutation]+", "+[LName]+", "+[FName])))))


Can you use this many arguments in a query? I didn't think you could.

thanks,
Full Williams

 

[Newman]

I know that there is a limit, but don't know the exact lenght.
One thing you could do is split your query into two. BUT, for myself, I once did a query with ~1500 caracters, and had to split it in two. So I believe that it should be ok with the one I gave you.
Let's take a look at it again...
I'm wondering about the "(" and the ")".
I have no time for it right now...
I'll come back later on...
Newman

 

[Newman]

I'm baaaack!
Replace the «+» with «&».
Replace the «,» that are not between «"» with «;».
And make sure you don't bring «Spaces» if you «Copy/paste» from the forum.
It works for me, I've done it during my lunch time.
You might have to work around the «,» that are between «"», cause they will appear even when the fields don't.
Newman

 

[Full_Williams]

Ok,

I've replaced all the «+»'s with «&»'s. I tried to replace the «,» that are not between «"» with «;» and I get a syntax error. Do all of these need to be replaced? If I keep all the «,»'s I get results, but they're not correct. In some cases I'll get 2 names next to each other.

H., Lusher, Ruth, H., , Lusher, Ruth

Any other suggestions?

Thanks for all your help.

Full Williams

 

[Newman]

For the «;» vs «,» case, it could be explained by the fact that I have the french version of Access.
(It is realy bad!)
For the duplicate case, I am surprise and I have no answers yet.
Sorry!
Newman

 

[Full_Williams]

Newman,

Thanks for all your help. Not sure if it has to do with your version not.

As it turns out they don't want the MidInitial in the Name now just Salutation, First Last Name which I can handle. Go figure. Thanks for your help though.

Full Williams