MS Access Report - Truncate comma if no more data

Hi Experts,

I have this in a text Box under Control Source:
=[FullName] & "," & " " & [DProSuffix]

Open in new window

This is a MS Access Report done in 2010
So I what the "," if there is more data to place on the report.

Mr. John Smith, Professor

But I don't want the "," (Comma) to display if no more data and currently it would show:
Mr. John Smith,

I want:
Mr. John Smith

Thanks for any help...
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
try this

=[FullName] & +", "+[DProsuffix]
Mike EghtebasDatabase and Application DeveloperCommented:
=[FullName] & iif(len([DProSuffix])>0,", ", "") & [DProSuffix]

or possibly:

=[FullName] & iif(len([DProSuffix])>0,", ", "") & nz([DProSuffix],"")

if [DProSuffix] may have null instead of empty string. Which with string properties null will not happen.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
I think Rey meant

=[FullName] & ", "+[DProsuffix]

This works because the & and the + work differently when one of the operands is null.  

"something " & ", " + null = "something"
"something" & ", " & Null = "something, "
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Mike EghtebasDatabase and Application DeveloperCommented:
Use of null (not applicable in this case) and "" (empty string, applicable here I guess) makes difference.

Sub test()
Dim a
a = "something " & ", " + ""
MsgBox a
a = "something" & ", " & ""
MsgBox a
End Sub

Open in new window

Here both message boxes produce "something,"
That is consistent with my explanation.

It happens because a ZLS is something.  It is not "nothing".  A null is "nothing".  So the + sees the ZLS as something and concatenates it even though it has a length of 0.  It is a subtle difference but "" is NOT the same as null.

If somefield = "" --- will return true if somefield contains a ZLS
If somefield = null --- will always return null/false regardless of the contents of somefield
if "" = null --- will always return null/false
Mike EghtebasDatabase and Application DeveloperCommented:
re:> That is consistent with my explanation.

Unless I am missing something, it is not consistent with what  you are stating:

Sub test()
Dim a
a = "something " & ", " + ""     ' this gives something,
MsgBox a
a = "something" & ", " & ""     ' so does this
MsgBox a
End Sub

Open in new window

Amour22015Author Commented:
Great thanks, used Mike Eghtebas first post.
The choice is yours of course but I'm not sure why.  Rey's suggestion is superior since it does the job without invoking a function.   It is a bit of an expert trick since it takes advantage of the difference in how the two concatenation operators work but it is a documented "trick".  Although Functions are extremely useful, they are expensive in terms of execution time and so if you can do something without invoking one (especially in a repetitive situation such as a report or a query or a code loop), you probably should.  

While I don't suggest that folks spend a lot of time on optimization, I do suggest that you learn the relative costs of operations and go light whenever possible.  Over time, a consciousness of the efficiency of operations will result in a lighter footprint and a livelier interface.
Mike EghtebasDatabase and Application DeveloperCommented:

re:> The choice is yours of course but I'm not sure why.

When I have hard question I seek Rey's help. No question he is one of the best.

I am surprised you insisting that your suggested solution works. It doesn't. Why you are not trying and testing it for yourself?

"something " & ", " + "" = "something," not "something"

Do you want Amour22015 to pretend that solution works?

I was actually suggesting that Rey's solution works.  As it happens, it would have been my suggestion also.

Text table columns should be defined to allow ZLS or null but not both.  As you can see, both creates a problem which forces you to use a second function.  One function would be required if you only allowed ZLS but no function is required if you only allow nulls.  If the table allows ZLS (which I don't recommend and which is why the solution always works for me) then you have no option but to use functions to determine the length of a field.  It wasn't clear from the description or the answer that the OP had a table that allowed ZLS.  My apologies if that is the case.  I didn't say that your solution didn't work.  It does work.  It is simply that if you don't have to contend with ZLS, then it is less efficient.  Since most of my work is with SQL Server and with tables that run to millions of rows, I have to be very conscious of avoiding functions in queries if there is no direct SQL Server equivalent because it could adversely impact the way Access requests the data from the server.
Mike, there is an apparent misunderstanding.  Pat actually corrected Rey's answer which had a typo (his expression had an extra + symbol).  Using the + sign to concatenate does work when used with a Null value.  Remember, it is more than likely that a blank field in a table is Null rather a Zero Length String so if you use Nulls in your code rather than a ZLS you'll see that it does work without a problem

Here's how you should have written your code:
Dim a
a = "something" & ", " + Null
MsgBox a
a = "something" & ", " & Null
MsgBox a

Open in new window

Mike EghtebasDatabase and Application DeveloperCommented:

What if user, at some point later, updates [DProsuffix] =""?

"something" & ", " & ""    = "something,"
"something " & ", " + ""   = "something,"

This is why Rey's solution will no longer work after such an update. I think  Amour22015 has selected the right solution for the project.

Thank you for the post; but Pat's explanations on this was very clear as well. If you and Pat agree with what I am saying, please both of you hit "Good Answer" on my solution. Otherwise, please let me know why I am wrong.

I'm not saying you're wrong, Mike; I was just pointing out that concatenation with the + sign works with NULL values and not with ZLSs.  I was mistakenly under the impression that you thought you could use the + sign to concatenate a ZLS but apparently we are all on the same page on this.  

As for clicking on Good Solution, my understanding is that its purpose is to recognize a solution that I personally wouldn't have thought off and that I find as a new way of accomplishing something in a more efficient or unique way than anything I've done before.   None of the solutions are new to me so I won't be doing that here.  If I am incorrect about the use of these new buttons, then please let me know and I'll concede.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.