How to handle an ampersand in Microsoft Access

I have a control on a form that populates a label on the opening of a report. Everything seems to work expect that the ampersand is not appearing. Is there a way to handle this so that the ampersand appears. I have attached the on open properties for the report. I have resorted to this method as there are some folks who do not have titles or are not associated with organizations and I don't want to have blank lines in the contact field, which would appear like this if all the data was present:

Name
Title
Organization
Address & Address1
City, State, Zip

Thanks experts!

Private Sub Report_Open(Cancel As Integer)
Dim strContactDetail As String
Dim sTitle As String
Dim sOrganization As String
Dim sAddress As String
Dim sCityStateZip As String
strContactDetail = Forms!frmPrintTaxLetter![ContactID].Column(2) & vbCrLf



'Clean up contact information for missing fields
       
        'Title cleanup
        If Forms!frmPrintTaxLetter![ContactID].Column(3) = "" Then
            sTitle = ""
       
        Else
            sTitle = Forms!frmPrintTaxLetter![ContactID].Column(3) & vbCrLf
           
        End If
       
           
        'Organization cleanup
        If Forms!frmPrintTaxLetter![ContactID].Column(4) = "" Then
            sOrganization = ""
       
        Else
            sOrganization = Forms!frmPrintTaxLetter![ContactID].Column(4) & vbCrLf
           
        End If
       
         'Address cleanup
        If Forms!frmPrintTaxLetter![ContactID].Column(5) = "" Then
            sAddress = ""
       
        Else
            sAddress = Forms!frmPrintTaxLetter![ContactID].Column(5) & vbCrLf
           
        End If
       
        'CityStateZip cleanup
        If Forms!frmPrintTaxLetter![ContactID].Column(6) = "" Then
            sCityStateZip = ""
       
        Else
            sCityStateZip = Forms!frmPrintTaxLetter![ContactID].Column(6) & vbCrLf
           
        End If
   
    Me.Organization.Caption = strContactDetail & sTitle & sOrganization & sAddress & sCityStateZip

End Sub
Harry BattDirector of DevelopmentAsked:
Who is Participating?

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

x
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.

dsackerContract ERP Admin/ConsultantCommented:
Use two ampersands (&&).
Harry BattDirector of DevelopmentAuthor Commented:
That solution doesn't work as this is not code, but rather the data is stored in a text field in the database (e.g. Peter, Paul & Mary, Inc.) and I wouldn't want to change it to be "Peter, Paul && Mary, Inc."
Nick67Commented:
I have a control on a form that populates a label on the opening of a report
The label is on a form? or on a report??
A label on a form is going to HAVE TO HAVE the double ampersand.
No biggie, since you are building the string anyway
Instead of
Me.Organization.Caption = strContactDetail & sTitle & sOrganization & sAddress & sCityStateZip

Try

Me.Organization.Caption =Replace(strContactDetail & sTitle & sOrganization & sAddress & sCityStateZip, "&", "&&")

I am looking into what happens if there are multiple ampersands in there, though!
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Nick67Commented:
Bad things happen!
So, don't use a label.
Change the control to a textbox, lock it, and screw with .Value instead of .Caption, unless you can absolutely guarantee a single ampersand in the string.

Want to see why a label is bad?
Try
Me.Organization.Caption ="hbatt && Nick67 &&&&&&&& dsacker" and look at the result!
or
Me.Organization.Caption ="hbatt && Nick67 && dsacker && @mx && @JDettman"
Weird!

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
Eric ShermanAccountant/DeveloperCommented:
I looked at this quickly and maybe a bit confused ...

Can you post a quick sample of what you are getting now on the report and a sample of what you want it to be???

ET
Harry BattDirector of DevelopmentAuthor Commented:
Nick--you went above and beyond the request and with a sense of humor as well. Although I certain that no one uses "&&" in the spelling of their firm or company, insurance is not a bad option. Thank you!
Nick67Commented:
Thank you!
Looking twice, I think first you'd like to read this
http://www.experts-exchange.com/articles/2040/Concatenation-in-Access-Reports-and-Forms.html
Don't skip down immediately to the multi-line concatenation part!
Read it all.

I don't care for all the
Forms!frmPrintTaxLetter![ContactID].Column(x)
It makes for 'unreadable' code, and what if you re-arrange the columns some day, or add new ones?
I'd go get the .RowSource and value of that puppy and then do the rest in a recordset.
Then you are referring to fieldnames, and not (x), which is more readable and more robust.
And what if values are Null instead of ""?

Still, I am glad I could help.

Nick67
Jeffrey CoachmanMIS LiasonCommented:
hbatt

1. In the beginning, always keep things simple and small, ...to test things to see if they even work.
For example, Why build a complex string variable before knowing if each segment will print properly?

2. Use a textbox not a label
Normally labels are for static text (text that does not change)
If you need to build and display complex string variables, ...you would do better to use a Textbox.


3. For me, I do not include the vbcrlf in the string variable, ...For clarity, (and better control), I concatenate it into the final string:
sBigVar=sVar1 & vbcrlf & sVar2 & vbcrlf  & sVar3

4. You did not state what section of the report this control was in.

5. You did not state why you are using the report open event and not the format event of the section the controls are in.

In any event, the report in the attached sample db and screenshot works for me...
Looks Good
JeffCoachman
Database80.mdb
Harry BattDirector of DevelopmentAuthor Commented:
Nick and Jeff,

Thanks again for more detailed explanations of how to concatenate more effectively and also for the advise. I did try the textbox.value approach first but received run-time error 2448 and that I can't assign a value to this object, which is why I went to the label.caption approach. I am still getting the run time error when I change the label to a textbox. Any idea why?
Jeffrey CoachmanMIS LiasonCommented:
Not sure why you are getting that error, (we don't know the full code you are using) ...unless you are trying to use a control that is Bound to a field (a textbox that has its controlsource set)
ex:
ControlSourcde=CustomerName

In any event, ...this seems to work OK in the sample I posted.

Try it again, ...if it still does not work, then post a simple sample of your database.

JeffCoachman
Harry BattDirector of DevelopmentAuthor Commented:
Thanks for making me be persistent on this matter. The textbox is not bound and is located in the detail section of the report. I received the error when it was an open event, but when I changed it to a load event, the textbox populated correctly. All is good and thanks again for your help.
Jeffrey CoachmanMIS LiasonCommented:
As I illustrated in my sample, the Open event will not work.
You need to run the code on the "Format" event of the section that the control is in...

Did you look at the sample I posted?
http://filedb.experts-exchange.com/incoming/2015/06_w27/921360/Database80.mdb

JeffCoachman
Harry BattDirector of DevelopmentAuthor Commented:
Jeff,

I did look at the sample you posted. While I found it helpful in some ways, and I appreciate the example, it also has some limitations. You have the report set to open in print preview, and my report opens in report view, which I prefer since I don't get the pesky magnifier glass and ribbon change. I have never used the on format property but it doesn't appear to work in report view. Also, your report seems to error when some of the fields are null, which I know can be fixed through code. I also found that the ampersand issue went away when I converted the label to a text box, so I have ditched the replace formatting. I am happy with the results and am using it now (with everything properly formatted) to generate some tax acknowledgement letters for my nonprofit organization.
Jeffrey CoachmanMIS LiasonCommented:
OK,

Glad we all could help.
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
Microsoft Access

From novice to tech pro — start learning today.