Update Query - change negative currency values to positive

[Guest]

Hello,

Access 2003 (Access 2000 file format)

I have a currency field in a table that I populated with both positive and
negative numbers. I would like to have all the numbers changed to positive.

I was thinking I could do this with an update query by putting something
like "abs([GrossAmount])" in the 'update to:' for the field - I've tried this
several ways and it doesn't change the value.

Any suggestions?

TDR

 

[Rick Brandt]

Hello,

Access 2003 (Access 2000 file format)

I have a currency field in a table that I populated with both
positive and negative numbers. I would like to have all the numbers
changed to positive.

I was thinking I could do this with an update query by putting
something like "abs([GrossAmount])" in the 'update to:' for the field
- I've tried this several ways and it doesn't change the value.

Any suggestions?

TDR

Are you "running" the update query or just switching to Datasheet view?
Otherwise I usually find I have ot include the table name qualifier...

=Abs([TableName]![GrossAmount])

 

[Guest]

I was just switching to Datasheet view

This is what I've got - and when I look in the datasheet view there is no
change:

UPDATE SystematicPlans SET SystematicPlans.GrossAmount =
Abs([SystematicPlans]![GrossAmount]);

Do I need to run the query to see the change?

Hello,

Access 2003 (Access 2000 file format)

I have a currency field in a table that I populated with both
positive and negative numbers. I would like to have all the numbers
changed to positive.

I was thinking I could do this with an update query by putting
something like "abs([GrossAmount])" in the 'update to:' for the field
- I've tried this several ways and it doesn't change the value.

Any suggestions?

TDR

Are you "running" the update query or just switching to Datasheet view?
Otherwise I usually find I have ot include the table name qualifier...

=Abs([TableName]![GrossAmount])

 

[Jeff Boyce]

If the negative amounts are meaningful (i.e., they were put there for a
reason), don't "update" them. Instead, create a query that returns all
values as positive.

And I would use the Abs([YourField]) function to do that -- what doesn't
work when you use it?

Good luck

Jeff Boyce
<Access MVP>

 

[MGFoster]

I was just switching to Datasheet view

This is what I've got - and when I look in the datasheet view there is no
change:

UPDATE SystematicPlans SET SystematicPlans.GrossAmount =
Abs([SystematicPlans]![GrossAmount]);

Do I need to run the query to see the change?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

A... yes. How else will the changes be made? With the query design
view, or SQL view, showing, from the main menu toolbar, select Query >
Run. Then look at the table. The value should be changed.

BTW, the correct separator between table names and column names is a
period, not an exclamation point.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQnKylIechKqOuFEgEQKi/QCg4E8us8QMJm5Ssk8rq+w480NQgPcAnjfp
yphIHGeDtz5q21S6ZtZ7GiD6
=kFRZ
-----END PGP SIGNATURE-----

 

[Guest]

Thanks - I assumed that I could view the changes the query was supposed to
make before actually making them by using the datasheet view to ensure the
query was going to do what I wanted it to do - since I can't undo an update
query (I think) - I suppose I could have made a backup of the table though.

After running the query - the changes were succesfully made - thanks again
to all

In this table I have another field which tells me whether the amount is a
withdrawal or deposit and I can use that field when I need to. Having the
amount as a negative was not necessary.

It is interesting that the '!' is not the appropriate separator - I see it
all the time in these posts so I assumed it was the way to go (and it worked)
- If you care to enlighten me on what the difference may be I would
appreciate it - otherwise I'll try to find the info myself.

Thanks again,

TDR

If the negative amounts are meaningful (i.e., they were put there for a
reason), don't "update" them. Instead, create a query that returns all
values as positive.

And I would use the Abs([YourField]) function to do that -- what doesn't
work when you use it?

Good luck

Jeff Boyce
<Access MVP>

Hello,

Access 2003 (Access 2000 file format)

I have a currency field in a table that I populated with both positive and
negative numbers. I would like to have all the numbers changed to
positive.

I was thinking I could do this with an update query by putting something
like "abs([GrossAmount])" in the 'update to:' for the field - I've tried
this
several ways and it doesn't change the value.

Any suggestions?

TDR

 

[MGFoster]

TDR wrote:

It is interesting that the '!' is not the appropriate separator - I see it
all the time in these posts so I assumed it was the way to go (and it worked)
- If you care to enlighten me on what the difference may be I would
appreciate it - otherwise I'll try to find the info myself.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


In Access the exclamation is an indication that the left side is an
object and the right side is an item in a collection of that object.
E.g.: FormName!ControlName - individual controls are in the Forms
default collection Controls. Another way to show that is:

FormName.Controls("ControlName")

The exclamation mark is a "shorthand" way to indicate the item in the
collection Controls. Another way:

FormName.Controls!ControlName

Though thinking in OOP (Object Oriented Programming) it would seem
proper (and, indeed, Access thinks so) that a column is in a table's
collection. But, in the SQL Standard definition of a table the columns
are attributes of an entity (the table) not part of a collection of
columns. The standard states that the entity's attribute designation is
defined like this:

<entity name>.<attribute name>

If you ever have to convert your Access/JET SQL to MS SQL Server, SQL'r
won't recognize the exclamation point as the separator between the table
name and the column name.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQnLqDYechKqOuFEgEQLzmQCcCL6n/JvULmSQ3Y80sQWlt+2B9y4AoKMA
gumWJQbvdslsxUBYy5CDOaxp
=FwPd
-----END PGP SIGNATURE-----