Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Imported data from Excel - want to keep formatting in a report

Posted on 2011-02-14
10
Medium Priority
?
239 Views
Last Modified: 2012-05-11
Hi all,

I have an Access database where I import recrds from Excel, format these records, and then output various reports in PDF format.  

Here is my issue:  

One of the fields I import contains one or more names of contacts (first and last name) for that record.  In the original Excel sheets they are entered into a single cell using Alt+Enter to place each name on its own line within the cell:

John Smith
Joe Doe

Upon import into my table this field is changed to put the names together as shown below:

John SmithJoe Doe

When I output this table as Excel it restores the format in a new Excel file with the original formatting (Alt+Enter).  This leads me to believe that the information is stored somewhere in Access.

When I create a report in Access to show these fields, the names are shown as they are on the table:

John SmithJoe Doe

I have the report fieds set to Can Grow and Can Shrink = Yes and the result is as follows:

John SmithJoe
Doe

Is it possible to have the report set to read the field as Excel does to have the names split and stacked as they were in Excel and as they are again when output to Excel?

Thank you for your help!
0
Comment
Question by:Hobart007
[X]
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
  • 6
  • 4
10 Comments
 
LVL 59

Expert Comment

by:HainKurt
ID: 34889460
try

repcale(name, chr(10), Chr(10)&Chr(13))

in your select statement...
0
 
LVL 59

Expert Comment

by:HainKurt
ID: 34889476
oops,

replace(full_name, chr(10), Chr(10)&Chr(13))

or

replace(full_name, chr(13), Chr(10)&Chr(13))
0
 

Author Comment

by:Hobart007
ID: 34889582
I am not sure what you mean by select statement.  (I am woefully bad at reporting in Access.)  I am not pulling the report through this name field but rather through department names sorted by due date.  The only selection criteria I have apears in the Filter field of the property sheet which is as follows:

[Resource Dept(s)]="Marketing"

The reports pull from a query which is created by pulling needed data from a table which is imported from Excel.

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!

 
LVL 59

Expert Comment

by:HainKurt
ID: 34889704
there should be a select statement somewhere

where do you get your data, from a table?

then create a query in access like

select t.*, replace(full_name, chr(10), Chr(10)&Chr(13)) full_name_formatted
from myTable t

and save this as qDepartments

then use query in your report instead of table... and use full_name_formattedin yourreport instead of full_name

0
 

Author Comment

by:Hobart007
ID: 34889944
Okay,  I am trying a twist on what you are saying which is as follows:

As I am already pulling the data off of a query, I am creating an update query to run with my other formatting queries to Update the name on my original table with the formatted name.  I am building it as follows:

UPDATE Import SET Import.[Resource Name(s)] = replace([Resource Name(s)],chr(10),Chr(10) & Chr(13));

Something is wrong but I am not sure what.  The query runs but I see no changes in the formatting on the reports or in the table that feeds the query.

So aside from getting this to work, is there no way to have a report recognize what Excel does upon Output to?

0
 
LVL 59

Expert Comment

by:HainKurt
ID: 34890029
does it support HTML

try

UPDATE Import SET Import.[Resource Name(s)] = replace([Resource Name(s)],chr(10),"<br>")
where Import.[Resource Name(s)] <> replace([Resource Name(s)],chr(10),"<br>");

UPDATE Import SET Import.[Resource Name(s)] = replace([Resource Name(s)],chr(10)&Chr(13),"<br>")
Where Import.[Resource Name(s)] <> replace([Resource Name(s)],chr(10)&Chr(13),"<br>")

if you see <br> in the format, try setting to enable HTML (if there is such a setting :)

0
 
LVL 59

Expert Comment

by:HainKurt
ID: 34890042
can you please post a sample access that shows this behaviour...

a sample table, with a few record, and a report that shows the problem...
0
 

Author Comment

by:Hobart007
ID: 34890376
Please find the current DB attached.  I have sample data made to populate the report entitled: rpt_Marketing.

I've attached a sample of the report pulled using this data as well.  You can see the issue in the "Resource Name(s)" column.
SMP-Task-Prioritization.accdb
rpt-Marketing.pdf
0
 
LVL 59

Accepted Solution

by:
HainKurt earned 2000 total points
ID: 34891227
got it :)

I changed the query of marketing report as

SELECT Import.[Resource Dept(s)], Import.End, Import.FileName, Import.[Doc Package], Import.Description, [Resource Name(s)], Replace([Resource Name(s)],Chr(10),"<br>") AS ResourceName, Import.Notes, Import.SID
FROM Import
WHERE (((Import.Status)<>"Complete" Or (Import.Status) Is Null))
ORDER BY Import.End DESC;
      
then for Resourcenames I used new field "ResourceName" instead of old one "Resource Name(s)" and changed the format of that field as RichText

result is :)
SMP-Task-Prioritization.accdb
0
 

Author Closing Comment

by:Hobart007
ID: 34891778
Awesome!  Thank you very much
0

Featured Post

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
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 …

722 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