Function help needed

[Joe Cilinceon]

What built in function will change the sign of a number from + to -.

 

[Rick B]

* -1

 

[fredg]

What built in function will change the sign of a number from + to -.

If the value is always a positive value:
= [YourValue] * -1
or
= - [YourValue]

If the value can be a negative value:
=IIf([YourValue]<0,[YourValue]*-1,[YourValue])

 

[Joe Cilinceon]

Thanks guys I though there might be a function that would do it.

 

[Joe Cilinceon]

One more question for you guys. How is the best way to set a field in a
table so its values are always a negative number. Also if they are positive
how is the best way to change them to negative.

 

[John Vinson]

One more question for you guys. How is the best way to set a field in a
table so its values are always a negative number. Also if they are positive
how is the best way to change them to negative.

1. Set a Validation Rule (in table design view) on the field of <0.
Set the validation text to "This number must be negative" or some
other suitable warning.

2. Create an Update query based on your table; select this field. Put
a criterion of

0

on the Criteria line, and

- [fieldname]

on the Update To line.

John W. Vinson[MVP]

 

[Guest]

I would not control that at the table level, Joe. I would do it at the form
level in the After Update event of the control where the number is:
Me.txtNegNumber = Abs(Me.txtNegNumber) * -1

The Abs function removes the sign bit from the number so that * -1 always
turns it negative.

 

[Joe Cilinceon]

I would not control that at the table level, Joe. I would do it at
the form level in the After Update event of the control where the
number is: Me.txtNegNumber = Abs(Me.txtNegNumber) * -1

The Abs function removes the sign bit from the number so that * -1
always turns it negative.

I did set it in the form as that is where it came from. I found a couple of
numbers (Credits Applied) that should have been show as a negative number in
the table. This way if they forget to enter with a negative amount it will
catch it so it isn't updated to the Table as a positive. Thanks Klatuu.

 

[Joe Cilinceon]

1. Set a Validation Rule (in table design view) on the field of <0.
Set the validation text to "This number must be negative" or some
other suitable warning.

2. Create an Update query based on your table; select this field. Put
a criterion of

0

on the Criteria line, and

- [fieldname]

on the Update To line.

John W. Vinson[MVP]

Thanks John I'll give it a try.