Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    May 2004
    Posts
    38

    Red face Unanswered: Access 2003 Cell size

    Hi,

    I'm trying to export an Access 2003 query into Excel 2003.
    The problem is, some of the Access fields are memos with text string
    lengths of thousands of characters; some individual
    fields are up to 32k in length !
    When I export these to Excel 2003, it's truncating them at about
    1024 characters.

    The funny thing is, when I copy and paste just one individual memo
    field into one individual Excel cell, it works, all the characters (even if
    over 1,024 characters in length) are copied and show (in the formula bar).

    I looked in Excel's Help, and couldn't find a way to increase cell sizes
    to accomodate long text strings.

    Anyone know how to get around this limitation problem with Excel ?

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,420
    Provided Answers: 7
    My Question Would be Is excel the right tool for the job
    32k length that's a lot of typing

    what about SPLITTING THE MEMO FEILD UP

    MID(MEMOFEILD,1,255)
    then
    MID(MEMOFEILD,256,511)
    then
    MID(MEMOFEILD,512,766)
    when to stop
    Last edited by myle; 06-05-06 at 22:22.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    I agree with MYLE, I doubt that excel is the right tool for this job! Being primarily a numbers crunching program, I have to wonder about importing non-numerical fields of 32k length. Perhaps if you could explain a little more bout what you're trying to accomplish someone hee might be able to help you.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Another issue with long text strings in Excel is autofitting the columns\ rows. Of course Myle's solution would get round this. As the two above - I would be concerned about Excel being considered a good tool for such large, qualitative fields.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    May 2004
    Posts
    38
    Thanks for the suggestions.

    Unfortunately I have to export these large fields to an excel database; it's the format another company wants.

    So I'm still trying to figure out how to fix this excel cell size limitation.

    I could have swore I saw on this forum (around a year ago), someone had a solution to this problem. If anyone knows/remembers, I'd appreciate
    ------------------------------------------------------------------------------------
    Update :

    Ooops, I just found out that it's my Access 2003 Query that's truncating results (from large memo fields) !
    So hopefully that's just the problem.
    Any ideas ?

    The SQL code for this query looks like this :
    ---------------------------------------------------------
    SELECT Count([PATIENT-TABLE].[MR Number]) AS [CountOfMR Number], [PATIENT-TABLE].[MR Number], [PATIENT-TABLE].[Past History]
    FROM [PATIENT-TABLE]
    GROUP BY [PATIENT-TABLE].[MR Number], [PATIENT-TABLE].[Past History];
    -------------------------------------------------------------

    Where [Past History] is the memo field I DON'T want truncated results.
    How should I change this ?
    Thanks !
    Last edited by Pathology; 06-06-06 at 10:58.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Is your query a UNION? Actually - fancy posting the SQL?

    Also - how do you get the data into Excel? Manually? Code (cell at a time or copyfromrecordset)? TransferSpreadsheet?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    From Excel Help on Specifications and Limits:

    "Length of cell contents (text)32,767 characters. Only 1,024 display in a cell; all 32,767 display in the formula bar."

    As I read this, the cell actually contains up to 32,767 characters, but only 1024 will display in the cell. I believe the post you were thinking of is here:

    http://www.dbforums.com/showthread.p...t=export+excel
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  8. #8
    Join Date
    May 2004
    Posts
    38
    Quote Originally Posted by Pathology
    Ooops, I just found out that it's my Access 2003 Query that's truncating results (from large memo fields) !
    So hopefully that's just the problem.
    Any ideas ?

    The SQL code for this query looks like this :
    ---------------------------------------------------------
    SELECT Count([PATIENT-TABLE].[MR Number]) AS [CountOfMR Number], [PATIENT-TABLE].[MR Number], [PATIENT-TABLE].[Past History]
    FROM [PATIENT-TABLE]
    GROUP BY [PATIENT-TABLE].[MR Number], [PATIENT-TABLE].[Past History];
    -------------------------------------------------------------

    Where [Past History] is the memo field I DON'T want truncated results.
    How should I change this ?
    Thanks !
    Sorry, here's the SQL code again (above).
    Like I say, the problem appears to be the Access Query is truncating my memo fields in it's results to 255 characters.
    How should I alter the SQL code ?
    Thanks for all your help/ suggestions !

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I've just created a local access table with memo field. Created a query on it. I manually copied & pasted the field value into excel and checked the length with =Len("A1") and it comes out over 16, 000 which is correct.

    I don't use Memos much. Maybe there is a gotcha I am missing.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Relevent to Missinlinq's...er... link - I am using XP.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    May 2004
    Posts
    38
    Quote Originally Posted by pootle flump
    I've just created a local access table with memo field. Created a query on it. I manually copied & pasted the field value into excel and checked the length with =Len("A1") and it comes out over 16, 000 which is correct.

    I don't use Memos much. Maybe there is a gotcha I am missing.
    The field value from the query ? or the table ?

    Because I know you can copy and paste from an Access table with no truncation problems.

    The problem I'm running into is the Access query itself. When I run it, it's truncating memo field data !
    You can see my SQL code above.

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Pathology
    The field value from the query ? or the table ?

    Because I know you can copy and paste from an Access table with no truncation problems.

    The problem I'm running into is the Access query itself. When I run it, it's truncating memo field data !
    You can see my SQL code above.
    From the query. What and where is your BE?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    May 2004
    Posts
    38
    Quote Originally Posted by pootle flump
    From the query. What and where is your BE?
    BE ? I'm not sure what you mean ?

    BTW, I'm kind of a noob with Access, I don't use visual basic or anything in creating the database.

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Sorry - BE = back end.
    Is the data on a database server (e.g. SQL Server - I imagine not)?
    Is the data stored in a separate Access Database?
    Is your data stored in the same database as the query you are running and copying from?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oh cr@p - just noticed it is a group by query.

    No - access will only display the first 255 chars in a group by. Apart from anything else - why should you be repeating 32K fields?
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •