Isnull and Concatenation

[Lhuffst]

I have a query that joins first name, last name and Middle Initial. On the middle initial, if there is one, we add a . to the end. If there isn't one, then return a blank. All of the sudden, the only thing that is returning is the first name. Individually the fields work fine, it is just when the concatenation is happening. Any ideas/suggestions?

Here is what we use:


Original line was:
FullName: [first_name] & " " & [mi] & IIf(IsNull([mi]),"",". ") & [last_name]

Then switched it to:

FullName: [first_name] & " " & IIf(Trim([mi] & "")="","",[mi] & ". ") & [last_name]


REsults should be:
Joe R. Smith or
Joe Smith
What I get is Joe in both cases

Thanks

 

[randy700]

Both work fine for me in a test db. Any chance you inadvertently changed the field names?


Randy

 

[Lhuffst]

That's what I had thought so I zoomed in on the field and did a cut and paste just to make sure and had the same results. I'm going to try and made the if statement on just the middle initital and then concatenate the 3 fields without an if. Thanks

 

[Hap007]

Give this a try:

FullName: [first_name] & IIf(Len(Trim([mi] & " "))= 0, " ", " " & [mi] & ". ") & [last_name]

Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[JoeAtWork]

My method:

FullName: [first_name] & " " & Nz([mi],"") & IIf(Nz([mi],"")="","",". ") & [last_name]

That should eliminate any chance of a Null getting into the string.

 

[Hap007]

Interesting how we each have our own method for handling nulls which is what always fails in this type of example.

Bottom line, if MS Access would just not fail when refering to nulls in text fields, all of our lives would have been much simplier.

Hap...


Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[dhookom]

You should be able to use:
[tt][blue]
FullName: [first_name] & " " & [mi] + ". " & [last_name]
[/blue][/tt]

Duane
Hook'D on Access
MS Access MVP

 

[Lhuffst]

Thanks to all of you for your suggestions. I have tried each one and still have the same results. Since it doesn't seem to like the calculated field, I put in the field names and they can see blanks (at least until I figure out what it doesn't like).
lhuffst

 

[Lhuffst]

Duane, That is similar to what I started with and it doesn't work. The users are ok with the spacing so I am going to call this a successful switch and work on this issue in my spare (yea right) time. Thanks again to everyone.
Lhuffst

 

[dhookom]

It works great for me in a sample table. Even your second expression should work. If it doesn't work for you then I would expect your MI field might not be null.
[tt]

First Name MI Last Name Your Expression My Expression
Nancy Davolio Nancy Davolio Nancy Davolio
Andrew A Fuller Andrew A. Fuller Andrew A. Fuller
Janet Leverling Janet Leverling Janet Leverling
Margaret C Peacock Margaret C. Peacock Margaret C. Peacock
Steven Buchanan Steven Buchanan Steven Buchanan
Michael R Suyama Michael R. Suyama Michael R. Suyama
Robert King Robert King Robert King
Laura E Callahan Laura E. Callahan Laura E. Callahan
Anne Dodsworth Anne Dodsworth Anne Dodsworth
[/tt]

Duane
Hook'D on Access
MS Access MVP

 

[Lhuffst]

That's a thought. I'll try isempty. Thanks