Solved

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

Posted on 2011-02-14
10
233 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 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 34889460
try

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

in your select statement...
0
 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
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 53

Expert Comment

by:Huseyin KAHRAMAN
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 53

Expert Comment

by:Huseyin KAHRAMAN
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 53

Accepted Solution

by:
Huseyin KAHRAMAN earned 500 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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

751 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