Make value entered negative

[tl mike]

I am working on a financial db and having problems with the returns on some of the reports in which I would rather see a negative amount then a positive amount to show that the money is being removed instead if added. I have searched to no luck on how to set the txt box to only show and save the value as a negative amount.

 

[RuralGuy]

ABS([YourValue])*-1
...will always give a non zero value as a negative number but I think that is not what you were looking for.

 

[tl mike]

I was hoping for actual code not an equation but if no one knows how I may have to result back to it.

 

[RuralGuy]

What I gave you *was* code if you put it in the ControlSource of a control.
=ABS([YourFieldName])*-1
...using Your Field Name of course.

 

[gemma-the-husky]

the thing is credits (income) are negative

however, assuming you have a query driving the report then you can in the field definition put = - fieldname, or something like

you have to be careful, as now debits will be negative though

i've seem some ssystems sotre everything as positicve, and then change the sign basded on the type of account.

------
if you have a P&L report say, and you want a profit, then its no so easy, as changing credits to plus values might affect the final totals

 

[tl mike]

I need it to save the data in the table as a negative amount so what evey type or qry or rpt I run it will show the data being negative

 

[RuralGuy]

Use code in the BeforeUpdate event of the control.
Me.YourControlName = ABS(Me.YourControlName) * -1

 

[tl mike]

Worked !! Thanks alot!!

I actually had to put it on the Afterupdate because I already had data in some of the records etc. but none the less works perfect

 

[RuralGuy]

Worked !! Thanks alot!!

I actually had to put it on the Afterupdate because I already had data in some of the records etc. but none the less works perfect

I do not see where that will help you at all. You *only* get BeforeUpdate and AfterUpdate events in a control when the user changes what is in the control. Are you trying to change existing records in a form without user intervention?

 

[RuralGuy]

You do know there are BeforeUpdate and AfterUpdate events for both Controls *and* Forms right? Which one were you using?

 

[tl mike]

I had to use the AfterUpdate becuase I had data already entered and when I tried to use the BeforeUpdate it would bring up an error. With the after update it worked without any problems.

 

[RuralGuy]

I had to use the AfterUpdate becuase I had data already entered and when I tried to use the BeforeUpdate it would bring up an error. With the after update it worked without any problems.

What error were you getting? Already having data entered in the table should have no effect on what you are doing. Are you talking about Control events or Form events?

 

[tl mike]

I set it on the control event in which it said that the macro or function set to the BeforeUpdate or validation property for this field is preventing Access to save the data

Ok I see

I set the forms event BeforeUpdate and see what you are saying


Thanks for the help and insight!!

 

[RuralGuy]

OK, the code should be in the AfterUpdate event of the Control rather than the BeforeUpdate event of the Control. Sorry.

 

[tl mike]

No worries