Results 1 to 9 of 9
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Right Justify A Text Field

    Right Justify A Text Field

    Using Access 2000 (9.0.4402) SR-1

    I have to prepare a table for export to another system. The table has a left justified 10 character text field that requires the value to be right justified 1 position from the right.

    I need some code/procedure that will determine the length of the input field and pad the output field with the correct number of spaces.

    Input Output

    1_________ ________1_
    12________ _______12_
    123_______ ______123_

    I would like to do this in an append query, if possible, any thoughts?

    Thanks, John Graves

  2. #2
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    London, Ontario, Canada
    Posts
    437
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Right Justify A Text Field

    You could create an expression in your query like: fldouput: Space(9-Len([fldinput])) & [fldinput] & " "

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

    Re: Right Justify A Text Field

    You'll also need to allow for odd and even counts of characters in your string because character spacing is not really even unless you use a fixed width font.
    Charlotte

  4. #4
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Right Justify A Text Field

    Hi Brian

    Thanks for response.

    I tried following code:

    addr_no_p: Space(9-Len([strLocStreetNo])) & [strLocStreetNo] & " "

    Works fine except:

    There are 6410 records in the append query.

    I Get Error Message:

    Insert set 785 field(s) to Null due to a type conversion error.

    There are 785 blank strLocStreetNo fields

    Is there a way to test and eliminate this error message?

    Thanks, John Graves

  5. #5
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    London, Ontario, Canada
    Posts
    437
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Right Justify A Text Field

    Hello John,
    Are there indeed "785 blank strLocStreetNo fields" ? If there are, what would you like to do with them? i.e. fill the created field with 10 spaces, leave the field empty? You should be able to achieve either of those options by wrapping the code in an If statement.

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Right Justify A Text Field

    I'm not really an Access programmer, but if I had to do this in Excell I would use something like this:

    <pre> Right(" " & Trim(strToRightJustify), 9) & " "
    </pre>


    Maybe you can adapt that to what you need.
    Legare Coleman

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

    Re: Right Justify A Text Field

    In VB you have an RSet function, but I haven't found an equivalent in VBA.
    Charlotte

  8. #8
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Right Justify A Text Field

    Though there is still a bunch of functions tucked under the covers that I'm still discovering, like the useful but hard to find Partition function <img src=/S/wink.gif border=0 alt=wink width=15 height=15>, I don't think there is a VBA equivalent of the RSet function. I've always used the technique Brian described above.
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  9. #9
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Right Justify A Text Field

    Hi Brian

    Thanks for your help, I included another soultion for interested parties.

    John Graves


    Works OK
    addr_no_p: IIf(IsNull(Space(9-Len([strLocStreetNo])) & [strLocStreetNo] & " "),"", Space(9-Len([strLocStreetNo])) & [strLocStreetNo] & " ")

    Works OK
    addr_no_p: Format([strLocStreetNo],"@@@@@@@@@") & " "

Posting Permissions

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