|  | Change values to negative amounts? | | |
| | | CW |  |
|  Posted: Tue Aug 26, 2008 12:31 pm Post subject: Change values to negative amounts? |  |
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 |
| |
| | | fredg | |
| | Posted: Tue Aug 26, 2008 12:48 pm Post subject: Re: Change values to negative amounts? | |
| |  | |
On Tue, 26 Aug 2008 07:31:01 -0700, CW wrote:
| Quote: | 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
|
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.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail |
| |
| | | Klatuu | |
| | Posted: Tue Aug 26, 2008 12:57 pm Post subject: RE: Change values to negative amounts? | |
| | | |
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" wrote:
| Quote: | 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 |
|
| |
| | | CW | |
| | Posted: Tue Aug 26, 2008 1:36 pm Post subject: RE: Change values to negative amounts? | |
| | | |
Aaargh! That easy, eh!
Thanks, both Fred and Dave
CW
"Klatuu" wrote:
| Quote: | 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" wrote:
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 |
|
| |
|
|
