> I have gone throught the group and Microsoft's online help and have
> seen many suggestions but I am still seeking clarity:
> 1. I have an excel spreadsheet. Column A contains text that may be
> greater than 255 characters.
> 2. I have an access database. I link (not import) to the contents of
> the excel spreadsheet. In the design view in access, Column A has the
> data type "memo".
> 3. The data in column A is truncated at 255 characters in the Access
> linked table and in any reports.
> On Microsoft's site, I have read:
> -- http://office.microsoft.com/en-us/access/HP010950951033.aspx
> A common import problem - truncated data
> If you find that Excel cells containing more than 255 characters are
> being truncated during import, see "Data Type" under Step 4: Things you
> should know before importing certain data types and elements, in the
> "Getting started with the import operation" section of this topic. This
> will explain that the Import Spreadsheet Wizard previews only the first
> 25 rows of Excel data in predicting the data type to assign to the
> field in the new table.
> If you have a cell that contains 440 characters in the 36th row of
> data, for example, Access will not see the data in that row, and,
> instead of creating a field with the Memo data type, the wizard will
> create a field with the Text data type. How could you avoid this
> particular problem? By cutting and inserting the 36th row of data above
> the 25th row in the Excel worksheet. It's also important to ensure that
> the data in the columns of your worksheet have a consistent data type
> from row to row.
> So I made sure I did this -- but I still have the truncation issue.
> This article gives me the hope that there is a solution although it may
> be limited to 'imported' data, not linked data. So, I looked further
> on Microsoft's site:
> -- http://office.microsoft.com/en-us/access/HP051885461033.aspx
> Some field values appear truncated in a linked table
> During linking, if Access encounters values in Excel that are longer
> than 255 characters, they are stored in a memo field that displays only
> the first 255 characters. The workaround for this limitation is to
> import the worksheet or named range, instead of linking to it.
> This article, however appears to dash my hopes of a solution. It
> clearly says that regardless of it being a memo field or not -- Access
> still will only display the first 255 characters.
> So here is my question:
> 1. Is there a way to link to an excel spreadsheet and have the Access
> database display more than 255 characters in a cell
> OK, wait I might have just figured something out.
> - When I look at the table view in Access for the linked data in Column
> A, it still only shows 255 Characters
> - I opened my report in design view and got the properties for the Text
> Box used to display the Column A data. I changed the 'Format' from "@"
> to blank. Then saved the report.
> - When I open the report in Preview all 255+ characters appear.
> To double check here is what I did ...
> - I closed everything down. I went into my Excel spreadsheet and added
> more text to one of the cells (even more than 255 characters).
> - I opened up Access. Data in Table is still truncated but the report
> preview shows all of the data, including the new text I added.
> So, I tried one more thing.
> - I opened Access. Data in table is truncated, so I added the word
> "truncated" to the 255 character string.
> - I checked the report, the 255 characters, plus the word truncated
> appear in the report. I close Access
> - I opened the linked spreadsheet in Excel -- the cell contains only
> the 255 characters and the word truncated.
> - I reopen access. The data doesn't display the word truncated, but
> the report does.
> So, I think I answered my own question. Yes there is a way to display
> more than 255 characters from a linked Excel spreadsheet, but not in
> the data table -- but in a report and only if the properties for the
> field have the "@" removed from 'format'. So long as I don't monkey
> with the data in the Access Table which will overwrite the Excel
> spreadsheet, I should have no problems ...
> Does this seem logical at all??