This might sound a bit convoluted, but I vaguely remember having a problem
similar to this.
Create a table that matches the structure of your query, changing the
errange calculated field in the new table structure to a memo field. Make a
copy of the query and change it to an append query and append it to your new
table. Then export the table to Access using your macro.
I believe what's happening is that Access reads the fields of the query and
treats your calculated field as a text field, and that's where you're running
into the 255 character maximum. If you append the data to a table where the
calculated field populates a memo field, your problem should be solved.
--
Kevin Backmann
Paul Dennis said:
Just tried, still get the same problem. If I take the Chr() out altogether I
still get the same problem.
Kevin B said:
Have you tried changing the line feed to a carriage return?
Instead of Chr(10) try Chr(13)
--
Kevin Backmann
:
Access 2003 SP2
The module strings together for each meeting [MDate] (date field) +
[Comments] (memo field), i.e. MergeStatusUpdates = MergeStatusUpdates +
F_Date + " " + F_Comments + Chr(10)
MergeStatusUpdate is the name of the module and is defined as a string.
Looking at the results of the query all is fine, it's just when it gets to
Excel that it truncates.
:
Check the table structure of the table that is the source of your query. The
field type is determined there, not in the query or macro. If the field is a
text field, you've maxed out the number of characters in Access.
BTW: What version of Access and Excel are you using?
--
Kevin Backmann
:
Since I'm doing the work in a Access Macro memo isn't available only String.
However String in a macro is unlimited. I used a query in Access to view the
results from the Macro and it returns all the characters, hence this leads me
to believe that it is how Excel received the data that truncates it down to
255.
:
Is the Access field that isn't importing correctly a MEMO field? If it
isn't, the character limit is not in Excel, it's the 255 character text field
limit in Access that's causing the problem. Excel column width limit is 255,
but the number of characters allowed in a cell 32,767, although Excel will
only display the first 1024 characters.
--
Kevin Backmann
:
I am using the TransferSpreadsheet function in an Access macro to send data
across to an Excel worksheet and all works well apart from one cell. The data
being sent across is over 255 chars and Excel seems to limit the cell to only
255 chars hence I loose the rest of the data.
Is there anything i can do to get around this?
