Solved

Format Access Report Using Commas

Posted on 2011-03-04
6
303 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

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…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

816 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

12 Experts available now in Live!

Get 1:1 Help Now