Format Access Report Using Commas

Posted on 2011-03-04
Last Modified: 2012-08-13
I have two fields (first name, last name) that I would like to show in my Access report as:
Planek, Nancy
How would I accomplish this?  Also, how would I create a multi-line address on the report when I have an address, city, state and zip field in the query.  The title of my report should have an & in it, but it keeps changing to a

Question by:nplanek
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 35040251
Use a query as your report source.

I'm assuming you now how to make a query but if not let me know.

1.  Open a new query
2.  Double on the field 'f'irst name' to add it to the query.
3.  Edit the text on the bottom where 'first name' has been added
4.  Edit 'first name' to say something like:
     FullName: [last name] & "," & [first name]

You can then use 'FulName' as if it was any other field in your report.

To use the '&' symbol in your report title and get rid of the underscore '_' simply use two and symbols '&&', don't worry only one will appear.

Good Luck


Author Comment

ID: 35040290
That worked great, but what about creating a multi-line address on the report when I have an address, city, state and zip field in the query.

Expert Comment

ID: 35040634
For the multi-line address you can concatenate fields and put "Chr$(10) & Chr$(13)" between them.  These are newline and carriage return characters.

SELECT ([Address1] & Chr$(10) & Chr$(13) & [Address2] & Chr$(10) & Chr$(13) & [City] & Chr$(10) & Chr$(13) & [State] & " " & [ZipCode]) As FullAddress FROM MyTable

would return something like:

123 Fourth Avenue
Apartment 567
IN 54321

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.


Author Comment

ID: 35058584
Do I put that statement in a query, because when I do my items don't appear on different lines they have a square symbol after them.
LVL 74

Accepted Solution

Jeffrey Coachman earned 500 total points
ID: 35060336
<Do I put that statement in a query, because when I do my items don't appear on different lines they have a square symbol after them.>
I thought you stated that this was to be in a "Report"?

If so then us a query like this:
SELECT Customers.CustomerName, Customers.Address, [City] & ", " & [Region] & "  " & [PostalCode] AS CSZ
FROM Customers;

Then create a report using the Report wizard to create your "Report" from this query.
("Selecting "Columnar" as the Layout)

If this is simply to be Address Labels , then there is a Label wizard to create this as well.

There are many ways to get the output you are requesting, it all depends on your preferences, skill level with Access Report design, or the exact output you are expecting.


Expert Comment

ID: 35062314
Like Jeff said!

You could set up a report using CanGrow and CanShrink to handle missing address lines too.  

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question