Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Jan 2002
    Location
    Amherst, Massachusetts, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Retention of Space Characters in Data Fields (2000 SR1a)

    I maintain a large database in Access. A state agency I need to report to no longer wants comma delimited files. In a giant step backwards, they want data reported in specified character-counted fields, with space characters added to each field as required to make up any deficiencies in width. In other words, if one were maintaining the data in a notepad file, it would be fine. Is there any way to introduce space characters to the left of text data that will be visible on screen and not ignored when Access reports the data?

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Retention of Space Characters in Data Fields (2000 SR1a)

    What you do is use the Fixed Width format to export the data. You don't try an maintain it that way in the database itself. You can't see the spaces anyhow on the screen, so there's no point in trying to fuss with them until you need to do an export.
    Charlotte

  3. #3
    New Lounger
    Join Date
    Jan 2002
    Location
    Amherst, Massachusetts, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retention of Space Characters in Data Fields (2000 SR1a)

    Are you saying that if I export a fixed width data field as ASCII text, it will always have the requisite number of space bar characters (ASCII 32)? Wouldn't I need to first convert to courier or some fixed width font, specify the size, and then do a calculation as to how wide the column must be to exactly contain the requisite number of characters? Even after doing this, it seems odd that Access would insert ASCII 32 characters, as opposed to simply recording the column width. Remember, the state agency wants a pure ASCII file, with actual space bar characters making up the balance of all reported fields. Am I incorrect in questioning this?

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retention of Space Characters in Data Fields (2000 SR1a)

    Right click on the table you want to export.
    Choose Export.
    Fill the file name you want for the ascii file and select Text files as Save as type.
    Click Save.
    In the next dialog box check Fixed Width - Fields are aligned in columns with spaces between each field.
    Click Next
    In these dialog box you can drag the lines between the fields to make them as large as you want or click on the advanced button to set some parameters as you want.
    Click on Finish and you will have a pure ascii file.
    Francois

  5. #5
    New Lounger
    Join Date
    Jan 2002
    Location
    Amherst, Massachusetts, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retention of Space Characters in Data Fields (2000 SR1a)

    I don't want *any* spaces between fields; I want ASCII 32 spaces *in* each field, with the number changing in every field according to the number of other text characters that happen to be present. For example, the state wants the last name field to always be justified left and space filled right, with a total field size of 20 characters. If the last name has 8 letters, then the state wants to see that field as the 8 letters followed by 12 ASCII 32 space characters. If the name has 4 letters, then the state wants the 4 letters followed by 16 ASCII 32 characters, etc. The number of space characters to insert will change with every record, but always to make up the specified number of characters. I need a way to make Access permanently record and treat as text all space entries entered in every record; or else I would need to write a macro that would count the normal text characters and enter the requisite space characters reliably upon export.

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retention of Space Characters in Data Fields (2000 SR1a)

    Spaces are Ascii 32.
    Try what I what I explain in my previous post and open the file in notepad. You will see that it works.
    Francois

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Retention of Space Characters in Data Fields (2000 SR1a)

    What Francois and Charlotte are trying to tell you is "Yes it does fill with spaces" when you use the fixed width option on the export wizard. You have to create an export specification to tell Access how wide each field is, and once you've done that it will create the fixed width text file. You may want to check and see if it has created "end of record" characters after each record - I haven't used it for years so I don't remember - but try it and you should see that it does exactly what the state has asked for.
    Wendell

Posting Permissions

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