Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Jul 2006
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Restrict spaces in text field (2003)

    I have a table with over 2 million records with a field "Id" made up of imported values from various sources. The text field "Id" is sized at 255. However, I need to export records with an "Id" field length of only 10. When I attempt to change the field length I get a message saying that I do not have the required disc space. I have been looking at restricting the spaces to 10 in a query and making another table with the right field length that way. Can anyone tell me how I do this (I see a space function, but I can't get it to work). Thanks v much!

    Ina

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Restrict spaces in text field (2003)

    What are you exporting to? Another Access database, or an Excel workbook, or a text file, or ...?

  3. #3
    Star Lounger
    Join Date
    Jul 2006
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Restrict spaces in text field (2003)

    A text file, as .xls will only take 65k records. Then I'm uploading to another application where the "Id" field is restricted to 10. At the moment, as there are both Alpha and Numeric values in field "Id", when I put into a text file, the export is adding spaces to the Alpha values - even if there are only 10 characters in the field (which there are), the text file will show "AAAABBB ". Does this help?

    Ina

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Restrict spaces in text field (2003)

    What is the name of the text file you are trying to export to?

  5. #5
    Star Lounger
    Join Date
    Jul 2006
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Restrict spaces in text field (2003)

    Hi hans - it's called TESTHIS.

    Ina

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Restrict spaces in text field (2003)

    You must use an extension .txt, .csv, .tab or .asc. Access will throw a fit if you try to import from or export to a text file with any other or no extension. So try TESTTHIS.txt as file name.

  7. #7
    Star Lounger
    Join Date
    Jul 2006
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Restrict spaces in text field (2003)

    Hi, Hans - no doesn't seem to be the problem you describe, as the export function (File/Export) automatically includes a .txt extension when you export as comma delimited text. I'm going to change this to a fixed length export using the advanced export function, and hopefully that will work. If not, I'll shout again!

    Thanks.

    Ina

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Restrict spaces in text field (2003)

    Oh. I thought you were already using fixed-width. I hope it works OK for you.

Posting Permissions

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