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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 245
  • Last Modified:

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

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
Hobart007
Asked:
Hobart007
  • 6
  • 4
1 Solution
 
HainKurtSr. System AnalystCommented:
try

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

in your select statement...
0
 
HainKurtSr. System AnalystCommented:
oops,

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

or

replace(full_name, chr(13), Chr(10)&Chr(13))
0
 
Hobart007Author Commented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
HainKurtSr. System AnalystCommented:
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
 
Hobart007Author Commented:
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
 
HainKurtSr. System AnalystCommented:
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
 
HainKurtSr. System AnalystCommented:
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
 
Hobart007Author Commented:
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
 
HainKurtSr. System AnalystCommented:
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
 
Hobart007Author Commented:
Awesome!  Thank you very much
0

Featured Post

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!

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now