Change values to negative amounts?

  • Thread starter Thread starter CW
  • Start date Start date
C

CW

I have a number of currency values in a table which should have been created
as negative amounts as they are for credit notes.
Unfortunately my database wasn't working properly until John Vinson helped
me out with the code to apply a minus sign if the current form was of type
"Credit Note".
That's working fine now, but I need to correct all those previous entries.
Rather than go through them all manually, could I do an update query and if
so, what would the syntax be, to change the field value from a positive into
a negative amount?
Many thanks
CW
 
I have a number of currency values in a table which should have been created
as negative amounts as they are for credit notes.
Unfortunately my database wasn't working properly until John Vinson helped
me out with the code to apply a minus sign if the current form was of type
"Credit Note".
That's working fine now, but I need to correct all those previous entries.
Rather than go through them all manually, could I do an update query and if
so, what would the syntax be, to change the field value from a positive into
a negative amount?
Many thanks
CW
Click to expand...

If I remember my childhood math, any number times a negative number
becomes a negative number. Also, any number times 1 is the same
number. Soooo..... +100 * -1 = -100

Update YourTable Set YourTable.FieldName = FieldName * -1

The above will reverse the sign of every value in [FieldName].
Note: if some of the values might already be negative, you should test
for that, otherwise it will make those existing negative values
positive.

Update YourTable Set YourTable.FieldName = FieldName * -1 Where
YourTable.FieldName >0

Only positive values will be changed.
 
Create an Update query.
In you query builder, you will want to filter for records that are credit
notes and where the amount is <0 (in case some are already correct)
The in the update to row of field you want to update, enter:

[FieldName] * -1

If you multiply a positive number by -1 it becomes a negative number. If
you multiply a negative number by -1 it becomes a postive number. For example

? 500 * -1 returns -500
? -500 * -1 returns 500
 
Aaargh! That easy, eh!
Thanks, both Fred and Dave
CW

Klatuu said:
Create an Update query.
In you query builder, you will want to filter for records that are credit
notes and where the amount is <0 (in case some are already correct)
The in the update to row of field you want to update, enter:

[FieldName] * -1

If you multiply a positive number by -1 it becomes a negative number. If
you multiply a negative number by -1 it becomes a postive number. For example

? 500 * -1 returns -500
? -500 * -1 returns 500

--
Dave Hargis, Microsoft Access MVP


CW said:
I have a number of currency values in a table which should have been created
as negative amounts as they are for credit notes.
Unfortunately my database wasn't working properly until John Vinson helped
me out with the code to apply a minus sign if the current form was of type
"Credit Note".
That's working fine now, but I need to correct all those previous entries.
Rather than go through them all manually, could I do an update query and if
so, what would the syntax be, to change the field value from a positive into
a negative amount?
Many thanks
CW
Click to expand...
Click to expand...
 
Back
Top