Format a currency field in a table to a negative value

[Diana van den Berg]

Is there a way to format a currency field in a table to a negative value?

Thank you for any help

[HansV]

Sorry, I don't understand the question. What exactly do you want?

[Diana van den Berg]

I have 2 currency fields that will always be negative values (bank charges) and I wondered if there was a way to make them negative from the get go, instead of having to remember to type in the minus sign in the form.

[Rudi]

I have 2 currency fields that will always be negative values (bank charges) and I wondered if there was a way to make them negative from the get go, instead of having to remember to type in the minus sign in the form.

You could just type something like this into the fields Format property: "R -"#,##0.00
Just remember...this is just a display format. To actually convert the value to negative, you need to update it with a query or update table (multiple the value with -1).

[HansV]

You wouldn't want to do that in the format of the field - you want the value of the field to be negative, not just its display.
You cannot do that in the table, but you can use the After Update event of a text box bound to the field on a form to make the value negative if the user forgot to do so. Let's say you have a field named BankCharge:

Code: Select all

Private Sub BanckCharge_AfterUpdate()
    If Me.BanckCharge > 0 Then
        Me.BanckCharge = -Me.BanckCharge
    End If
End Sub

[Diana van den Berg]

Yes, that was what I meant, Hans, though I didn't express it well.

Thank you so much for that! It works perfectly!

I am just curious to know why nothing is displayed in the AfterUpdate property for the unbound fields.

Thank you Rudi. I expressed it badly. I did mean the value.

[Rudi]

The After_Update property is an event property where a function or code usually goes.
Unless you attach something to it, it would always be blank (for bound or unbound fields).

[Diana van den Berg]

Thanks for the explanation, Rudi.