Solved

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

Posted on 2011-02-14
10
232 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
  • 6
  • 4
10 Comments
 
LVL 51

Expert Comment

by:HainKurt
ID: 34889460
try

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

in your select statement...
0
 
LVL 51

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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 51

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 51

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 51

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 51

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
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…
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.
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 …

820 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