Problem with truncating data

[Sandpiper]

Hi

I'm trying to create a report in Access 2010 based on data stored in Excel. I have linked the excel table to access, but have found that some of the data has been truncated.

I've been looking for work around for this all afternoon, and have seen lots of posts about reasons if you are trying to import/append etc, but nothing to do with just trying to view the data.

I'm not trying to import it into a table (given up on that), i was going to work directly with the Excel linked table, however, even when I just view the data, it has been truncated. Is there a setting in either access or excel that I am missing that will allow all of the data to show.

Thanks in advance

 

[Margarita]

Are you sure it has been truncated? When you set focus to the cell holding the number, do you see it expand and give you more precision (i.e. more digits after the decimal point)? If it DOES expand into more digits when you click on it, then the number is not really truncated- it's just that it's Decimal Places property is set to a smaller number than you wish to see. Go into the design view of the lnked table, click on the field that you want to see more digits for and change the number of decimal places to whatever level of precision you want.

I don't think anything gets truncated when you just link the data- linked data belongs to the source, so if your excel file has more than 2 decimal places and in the linked access table you see it with 2 decimal places, it's just a matter of how it is displayed- the data itself hasn't been changed.

By the way, why are you giving up on importing? What is the issue there?

 

[Sandpiper]

Hi
Thanks for your response, sorry I should have made it clear that it is a text field, so its the number of text characters that have been truncated, not a number.

I was hoping that by just linking to the data and not importing, it would allow me to use all of the data that is needed in the report, but even if I view the data in the linked table, 1/2 of it is still missing.

Any ideas?

Thank you

 

[Sandpiper]

HI
I have now found a solution or work around to my problem that may be helpful to others.
Instead of trying to link to the Excel table, go to the External Data tab, click Import the source data into a new table in the current database, then on the 3rd stage of the import wizard change the data type all of the relevant fields to "Memo". This then creates a table in my database with all data present. I can then use an append query to add the records to the required table.

Thanks

 

[jdraw]

Be cautious of memo fields, see

http://allenbrowne.com/ser-63.html

 

[Margarita]

HI
I have now found a solution or work around to my problem that may be helpful to others.
Instead of trying to link to the Excel table, go to the External Data tab, click Import the source data into a new table in the current database, then on the 3rd stage of the import wizard change the data type all of the relevant fields to "Memo". This then creates a table in my database with all data present. I can then use an append query to add the records to the required table.

Thanks

I am glad that you found a solution, but I'm just curious why you were having problems with text truncation to begin with. How long was the longest string in your original excel sheet?