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

TDR wrote:
> 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])

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

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?


"Rick Brandt" wrote:

> TDR wrote:
> > 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])
>
> --
> I don't check the Email account attached
> to this message. Send instead to...
> RBrandt at Hunter dot com
>
>
>

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>

"TDR" <(E-Mail Removed)> wrote in message
news:A6DC87CF-EA48-4D97-9927-(E-Mail Removed)...
> 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

TDR wrote:
> 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-----

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
"Jeff Boyce" wrote:

> 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>
>
> "TDR" <(E-Mail Removed)> wrote in message
> news:A6DC87CF-EA48-4D97-9927-(E-Mail Removed)...
> > 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
>
>
>

TDR wrote:
<snip>
> 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-----