Solved

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

Posted on 2011-02-14
10
235 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 56

Expert Comment

by:HainKurt
ID: 34889460
try

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

in your select statement...
0
 
LVL 56

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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 56

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 56

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 56

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 56

Accepted Solution

by:
HainKurt 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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

632 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