Right Justify A Text Field

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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],”@@@@@@@@@”) & ” “

Reply | Quote

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

 Right("         " & Trim(strToRightJustify), 9) & " "

Maybe you can adapt that to what you need.

Reply | Quote

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

Reply | Quote

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 , I don’t think there is a VBA equivalent of the RSet function. I’ve always used the technique Brian described above.

Reply | Quote