Convert a positive number to negative based on another field in a Table

[wazniw]

Dear Friends,

I am trying to create a table for income and expense [catergory] and would like the amount being entered into the [amount] field to have a negative or positive value on entering based on the category chosen.

so in my table I have

[catergory] which is chosen from a look up table ( which is either an INCOME or EXPENSE )
[amount] which is entered in the next field ( which has to return a positive or negative value based on the catergory choosen upon entering the data)

I would like to go one step further and indicate this negative value in red is possible in my FORM.




Hope you can help me- as I am unable to proceed at this point.

Thank you very much

[June7]

It isn't actually necessary to save the value as a negative. Can calculate the negative in a query with an IIf expression.

IIf([Category]="Expense",-1,1)*[Amount]

You could use conditional formatting for the color.

[rzw0wr]

On the AfterUpDate event of the Category enter me.amount = "-" & me.amount
For the amount when negative, use conditional formatting {Field Value is.....less than 0} This is a new rule. then add the text formatting.

Dale

[June7]

Might want to use conditional with that code and instead of concatenation, do math:

Me.Amount = IIf(Me.Category = "Expense", -1, 1) * Abs(Me.Amount)

Just in case the user changes the Category. That's the problem with saving calculated data (data dependent on other data) - if not careful the data can get 'out of sync'. That's why saving calculated data is usually not a good idea. Do calculations in queries and in textboxes.