Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Format Access Report Using Commas

Posted on 2011-03-04
6
Medium Priority
?
318 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 2000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

578 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