Solved

Format Access Report Using Commas

Posted on 2011-03-04
6
304 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Many companies are making the switch from Microsoft to Google Apps (https://www.google.com/work/apps/business/). Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

828 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