Harry Batt
asked on
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!
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
dsacker
Use two ampersands (&&).
ASKER
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."
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!
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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
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!
Thank you!
Looking twice, I think first you'd like to read this
https://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
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...
JeffCoachman
Database80.mdb
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...
JeffCoachman
Database80.mdb
ASKER
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?
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?
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=CustomerNam
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
ex:
ControlSourcde=CustomerNam
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
ASKER
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.
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
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
ASKER
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.
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.
OK,
Glad we all could help.
Glad we all could help.