Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Dec 2003
    Location
    Virginia, USA
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Space filling fields (97)

    I have created a large database. The records HAVE to be 512 bytes in size. I have created a template. What I need to do, is space fill the fields in the record. The fields are set accordingly for how many spaces they should be. Some fields will not get any data, but they still need to be space filled. Can this be done in Access?

    TIA!

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

    Re: Space filling fields (97)

    Access automatically trims trailing spaces in fields when they are edited, so it is difficult, if not impossible in Access itself. You can, however, export a table to a fixed width text file. Access will pad fields with spaces then.

  3. #3
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Space filling fields (97)

    You can use the Space function in an update query to add "padding" to a field in a table. According to Help, this function "returns a Variant (String) consisting of the specified number of spaces" (the numerical argument). And: "The Space function is useful for formatting output and clearing data in fixed-length strings." Example of use in update query:

    UPDATE Table2 SET Table2.Field2 = [Field1] & Space$((512-LenB([Field1]))/2);

    In this example, the data in Field1 (a standard text field) is copied to Field2 (a memo field), with extra spaces to result in text that is exactly 512 bytes in length. To verify results, run query like this:

    SELECT Table2.Field2, LenB([Field2]) AS [Length in Bytes]
    FROM Table2;

    After running update query, the Length in Bytes for each record in Field2, including spaces, was exactly 512. Note, however, I am using Access 2000 (A2K), not Access 97. A2K uses Unicode, which requires 2 bytes for each character. To use this in ACC 97 you will probably have to modify the formula in the update query:

    UPDATE Table2 SET Table2.Field2 = [Field1] & Space$(512-LenB([Field1]));

    I have no way of testing this, so you will have to experiment to get right results in ACC 97. Also note, the maximum size of a Text field in Access in 255 characters, which in A2K is 510 bytes, so in any event you need to define a Memo field for this purpose.

    HTH

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Space filling fields (97)

    Where did you get the 510 from Mark? Do you need to set an 'Option' field?

    The size of the field in A2002 is still 255, I have just tried it.

  5. #5
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Space filling fields (97)

    510 refers to length in bytes, not characters, which is still 255. As test, defined text field Field3, size = 255. Ran update query:

    UPDATE Table2 SET Table2.Field3 = [Field1] & Space$((512-LenB([Field1]))/2);

    Then ran this query:

    SELECT Table2.Field3, Len([Field3]) AS [Length in Characters], LenB([Field3]) AS [Length in Bytes]
    FROM Table2;

    Because Field3 is text, not memo, it maxed out at 255 characters. See attached pic for query results after update. As noted, since text is saved in Unicode in A2K or later, the length in bytes will be double the length in characters.

    HTH
    Attached Images Attached Images

  6. #6
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Space filling fields (97)

    On additional note - I didn't notice that some fields may be blank, but still need to be padded with spaces. If that's the case, use modified version of query:

    UPDATE Table2 SET Table2.Field2 = [Field1] & Space$((512-LenB(Nz([Field1])))/2);

    If Field1 is Null, NZ function will return a zero-length string, and the calculated expression will result in a string of 256 spaces, 512 bytes wide. If for some reason the spaces need to be Unicode so-called "No-Break Space" spaces (Unicode U+00A0), you could use String function in place of Space function in query. Example:

    UPDATE Table2 SET Table2.Field2 = [Field1] & String$((512-LenB(Nz([Field1])))/2,ChrW$(160));

    For String function, you first specify the length in characters, then the character to return. (Hex 00A0 = Decimal 160; ChrW$ function returns a Unicode character.) I don't know why the fields must be exactly 512 bytes; if reason is so data can be exported to another application that requires data in that size, it'd be more efficient to save data in Accesss in a text field (w/o the extra spaces), then add the spaces as required when exporting data. Example of query used as export data source:

    SELECT [Field1] & Space$((512-LenB(Nz([Field1])))/2) AS Field1X
    FROM Table2;

    This will add the extra spaces when exporting data, for example to a fixed width text file. As noted previously, you'd have to modify these examples to work properly in Access 97.

    HTH

Posting Permissions

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