Accounting Number format in Access

[klyhthwy]

Does anyone know how to format a currency text box in Access 2007 to appear like the accounting number format in Excel? I would like the dollar sign left aligned in the field, but the numbers right aligned. This looks more professional to me.

for example:

$ 3,344.00
$ (4,324.34)

not

$3,344.00
($4,342.34)

Thanks

 

[CharlesChuckieCharles]

so you'd take your number Myval

you are effectively turning the number to a string then pushing to the right with spaces then sticking a dollar on the left

assumes your label is 25 characters wide

obviously you could do this to a text box

in the after update

Private Sub Text1_AfterUpdate()
if isnumeric(me.text1.value) then
me.text1.value="$" & Right(Space(24) & Trim(Str(me.text1.value)), 24)
endif

End Sub

 

[klyhthwy]

The bound table field is formatted as currency. I believe that would only work if the field was a text format.

That is a good solution, though.
Thanks for your help!

 

[xenou]

I've not heard of accounting format in Access (in terms of the equivalent format in Excel). If you find success -- without converting to text -- post back, as that would be interesting. otherwise, normal currency format is probably more typical for Access.

 

[Kreszch68]

In Access 2003 you simply have to set the format to

€ * #.##0,00 for the positives
€ * \-#.##0,00 for negative numbers

So you'll end up with this in the format section

€ * #.##0,00;€ * \-#.##0,00


Somehow this doesn't work in Access 2007, at least not for me

PS Of course you have to replace the € for $ or whatever your currency is.

$ * #.##0,00;$ * \-#.##0,00

 

[Kreszch68]

I'm afraid it is a bug in Access 2007.
Tried on several Access 2007 compu’s, all with the same problem.
The best other option for me would be to add a ‘currency’ field to the table. Then place the control just over the control with the amount, this will look the same as if it was formatted. A little bit more work, but the good thing is you have a multi currency enabled database.

And if you are not using a continuous form, and don’t mind the font to be courier new 8 pt, you can use this code to format the controls

Dim s As String
Dim lVal As Long
Dim lBox As Long
Dim nS As Long
Dim ctl As Control
For Each ctl In Me.Controls
    Select Case ctl.Tag
    
    Case "€" 
        ctl.FontName = "Courier New"
        ctl.FontSize = "8"
        lBox = ctl.Width / 115
        lVal = Len(Round(ctl, 0))
        nS = (lVal - 2) / 3
        s = Space(lBox - lVal - 3 - nS)
        ctl.Format = "€" & s & "#,##0.00"
    End Select
Next ctl