Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Sep 2006
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello All -

    I am linking an Excel spreadsheet into an Access 2003 database, and I'm running into a problem with one of the fields. One of the records has a field, as shown in Excel, that is 407 characters in length. I know that Access has a maximum character field length of 255, but that is ok. If I look at the table, via Access' table view, I see the first 255 characters, of this 407 character length field, and everything appears fine. However, when I attempt to access the table via a Select Query with criteria on this particular field, the query bombs. I receive a message box that reads 'Unknown'. If I delete this 407 character field/record from my Excel spreadsheet, the problem is resolved.

    I'm trying to figure out why the Select Query doesn't work, even though the Access table is only returning 255 characters for this field. Hopefully, I've explained this clearly enough.

    Any help would be greatly appreciated.

    Thanks in advance.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    What kind of criteria are you trying to apply? In a quick test Access seems to handle simple criteria correctly in a query based on a linked Excel table with text values longer than 255 characters.

  3. #3
    New Lounger
    Join Date
    Sep 2006
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='785429' date='20-Jul-2009 15:11']What kind of criteria are you trying to apply? In a quick test Access seems to handle simple criteria correctly in a query based on a linked Excel table with text values longer than 255 characters.[/quote]

    If I enter criteria, for example, of GUARD, for this particular field, the query works. However, if I enter criteria of Like "*GUARD*", the query fails. It's rather strange how the query fails...can't say I've ever seen this type of problem before.

    I've attached a copy of the 'Unknown' error that is received.

    Thanks for responding Hans.
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Hmm, such a condition works OK for me in Access 2002.

    Do you have Access 2003 SP3?
    If not, try installing that.
    If you do, have you installed the Access 2003 post-Service Pack 3 hotfix package: December 18, 2007?
    If not, please do - it corrects several bugs.

  5. #5
    New Lounger
    Join Date
    Sep 2006
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='785434' date='20-Jul-2009 15:31']Hmm, such a condition works OK for me in Access 2002.

    Do you have Access 2003 SP3?
    If not, try installing that.
    If you do, have you installed the Access 2003 post-Service Pack 3 hotfix package: December 18, 2007?
    If not, please do - it corrects several bugs.[/quote]

    I have SP3 installed, and I've applied the HotFix package to no avail. I know that I can resolve the problem by changing the 407 character field to 255 via abbreviations and such...just stumped as to why it's not working.

    Thanks for the help Hans

Posting Permissions

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