MS Access - Right Align Formatted number on a Form

Question

If I use Standard format for a number in a control then I can Align Right and it appears on the far right of the control in the Form. However, I am using numbers in millions and so want to format the number so that, for example, 50,123,456 will appear as 50.123. So I use format #,,.0 with decimal places set to 3. If I set alignment to left, it aligns to the left of the field on the form, Align Center and it appears in the center. But Align Right and there is a large space to the right of the number, like padding. Is there a way to get the formatted number to hit right up to the right side on the form?

Answer 1

The formating option might not be the right approach to getting the needed solution. Do the following instead

1. Put the format to general number.

2. Put the decimal places value to 0.

Then go to VBA and add the following code to the after update event of the control

    Private Sub allocatedamount_AfterUpdate()
        Me.allocatedamount = Left((Me.allocatedamount / 1000000), 6)
    End Sub

In this case i added the code to the after update evnt of the control on the form named allocatedamount

I have attached two images.

This code will work for numbers between 10 million and 99 million, where it is between 100 million and 999 million you will have to add another zero to the constant 1000000 used in the VBA code,so that will entail using an If statement to first determine if the value is between 10 million and 99 million or 100 million to 999 million.