Solved

Format Access Report Using Commas

Posted on 2011-03-04
6
302 Views
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 -....help.

0
Comment
Question by:nplanek
6 Comments
 
LVL 1

Expert Comment

by:reginaldmerritt
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


0
 

Author Comment

by:nplanek
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.
0
 
LVL 8

Expert Comment

by:Andrew_Webster
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.

e.g.
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
Dogpatch
IN 54321

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:nplanek
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.
0
 
LVL 74

Accepted Solution

by:
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.

JeffCoachman
0
 
LVL 8

Expert Comment

by:Andrew_Webster
ID: 35062314
Like Jeff said!

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

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

The canonical version of this article is on my web site here: http://iconoun.com/articles/collisions/ A companion presentation is available here: http://iconoun.com/articles/collisions/Unicode_Presentation.pdf
The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

759 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now