How to make all negative values positive?

Is there an easy way to make all negative values positive in a particular
table?

I've been experimenting with this:

Dim rst As DAO.Recordset
Set rst = db.OpenRecordset("tblNegValues")

Do
If rst!Amount < 0 Then rst!Amount = rst!Amount - (rst!Amount * 2)
Loop

Is there a better way?

"deko" <dje422@hotmail.com> wrote in message
news:ONjHb.2907$NJ7.1857@newssvr29.news.prodigy.co m...[color=blue]
> Is there an easy way to make all negative values positive in a particular
> table?
>
> I've been experimenting with this:
>
> Dim rst As DAO.Recordset
> Set rst = db.OpenRecordset("tblNegValues")
>
> Do
> If rst!Amount < 0 Then rst!Amount = rst!Amount - (rst!Amount * 2)
> Loop
>
> Is there a better way?[/color]

The Abs function?

I'll bet Abs is more efficient than multiplication and subtraction, but I
still need a way to apply this to an entire table. Should I use For Each?
Must I use a Recordset operation here?

Thanks!

"rkc" <rkc@yabba.dabba.do.rochester.rr.nope> wrote in message
news:fbkHb.2307$q55.1700@twister.nyroc.rr.com...[color=blue]
>
> "deko" <dje422@hotmail.com> wrote in message
> news:ONjHb.2907$NJ7.1857@newssvr29.news.prodigy.co m...[color=green]
> > Is there an easy way to make all negative values positive in a[/color][/color]
particular[color=blue][color=green]
> > table?
> >
> > I've been experimenting with this:
> >
> > Dim rst As DAO.Recordset
> > Set rst = db.OpenRecordset("tblNegValues")
> >
> > Do
> > If rst!Amount < 0 Then rst!Amount = rst!Amount - (rst!Amount * 2)
> > Loop
> >
> > Is there a better way?[/color]
>
> The Abs function?
>
>
>
>
>[/color]

Use an update query that only contains the Amount field. Put this expression
where it says Update To:
IIF([Amount] < 0,[Amount]*-1,[Amount])

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
resource@pcdatasheet.com
www.pcdatasheet.com

"rkc" <rkc@yabba.dabba.do.rochester.rr.nope> wrote in message
news:fbkHb.2307$q55.1700@twister.nyroc.rr.com...[color=blue]
>
> "deko" <dje422@hotmail.com> wrote in message
> news:ONjHb.2907$NJ7.1857@newssvr29.news.prodigy.co m...[color=green]
> > Is there an easy way to make all negative values positive in a particular
> > table?
> >
> > I've been experimenting with this:
> >
> > Dim rst As DAO.Recordset
> > Set rst = db.OpenRecordset("tblNegValues")
> >
> > Do
> > If rst!Amount < 0 Then rst!Amount = rst!Amount - (rst!Amount * 2)
> > Loop
> >
> > Is there a better way?[/color]
>
> The Abs function?
>
>
>
>
>[/color]

Deko,
Why won't a simple update query do the trick for you?

UPDATE tblNegValues SET tblNegValues.Amount = -[Amount]
WHERE tblNegValues.Amount < 0

--
Hope this helps
Arno R

"deko" <dje422@hotmail.com> schreef in bericht
news:ONjHb.2907$NJ7.1857@newssvr29.news.prodigy.co m...[color=blue]
> Is there an easy way to make all negative values positive in a particular
> table?
>
> I've been experimenting with this:
>
> Dim rst As DAO.Recordset
> Set rst = db.OpenRecordset("tblNegValues")
>
> Do
> If rst!Amount < 0 Then rst!Amount = rst!Amount - (rst!Amount * 2)
> Loop
>
> Is there a better way?
>
>[/color]

dje422@hotmail.com (deko) wrote in
<TzkHb.2919$ib.1595@newssvr29.news.prodigy.com>:
[color=blue]
>I'll bet Abs is more efficient than multiplication and
>subtraction, but I still need a way to apply this to an entire
>table. Should I use For Each? Must I use a Recordset operation
>here?[/color]

If you're changing all the values in a column in a table, or a
subset of values that match certain criteria, SQL is definitely the
way to go. Arno has given you one such solution.

A SQL UPDATE is almost always going to be faster than walking
through a recordset and changing one row at a time, though there
are some kinds of changes that can't really be done with SQL, so
you sometimes do need to walk a recordset. But it's not nearly as
often as many people think (especially people who come from an
xBase environment).

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

Thanks for the reply.

You're right - an update query is the way to go. "Amount = -Amount" is
simple enough, and works great. I was focusing too much on the arithmetic
operations I thought were necessary to convert the negative to a positive
and didn't think I could do it with SQL.

I think Abs is a good way to go, too (rck's suggestion).

UPDATE tblNegValues SET Amount = Abs(Amount) WHERE Amount < 0

"Arno R" <arracomn_o_s_p_a_m@tiscali.nl> wrote in message
news:3fedd7f1$0$41765$5fc3050@dreader2.news.tiscal i.nl...[color=blue]
> Deko,
> Why won't a simple update query do the trick for you?
>
> UPDATE tblNegValues SET tblNegValues.Amount = -[Amount]
> WHERE tblNegValues.Amount < 0
>
> --
> Hope this helps
> Arno R
>
>
>
> "deko" <dje422@hotmail.com> schreef in bericht
> news:ONjHb.2907$NJ7.1857@newssvr29.news.prodigy.co m...[color=green]
> > Is there an easy way to make all negative values positive in a[/color][/color]
particular[color=blue][color=green]
> > table?
> >
> > I've been experimenting with this:
> >
> > Dim rst As DAO.Recordset
> > Set rst = db.OpenRecordset("tblNegValues")
> >
> > Do
> > If rst!Amount < 0 Then rst!Amount = rst!Amount - (rst!Amount * 2)
> > Loop
> >
> > Is there a better way?
> >
> >[/color]
>
>[/color]

"deko" <dje422@hotmail.com> wrote in news:ONjHb.2907$NJ7.1857
@newssvr29.news.prodigy.com:
[color=blue]
> Is there an easy way to make all negative values positive in a particular
> table?
>
> I've been experimenting with this:
>
> Dim rst As DAO.Recordset
> Set rst = db.OpenRecordset("tblNegValues")
>
> Do
> If rst!Amount < 0 Then rst!Amount = rst!Amount - (rst!Amount * 2)
> Loop
>
> Is there a better way?[/color]

I suppose (whimsically), that you are storing a calculated field if you do
this, and that you should leave your data as it is, and simply use and/or
display the absolute value of these values.

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

hi,

update MyTable
Set Amount=(Amount*-1)
where Amount <0

"deko" <dje422@hotmail.com> schrieb im Newsbeitrag
news:ONjHb.2907$NJ7.1857@newssvr29.news.prodigy.co m...[color=blue]
> Is there an easy way to make all negative values positive in a particular
> table?
>
> I've been experimenting with this:
>
> Dim rst As DAO.Recordset
> Set rst = db.OpenRecordset("tblNegValues")
>
> Do
> If rst!Amount < 0 Then rst!Amount = rst!Amount - (rst!Amount * 2)
> Loop
>
> Is there a better way?
>
>[/color]

Actually, it's part of an import process that downloads bank statements and
populates a table with all the various transactions. The database then
crunches the numbers, which need to be all positive values. But the
different banks are not consistent in how they represent the amounts - some
are negative (2,483.39), some are positive $2,483.39.

This seems to do the trick:

UPDATE tblTransactions SET Amount = Abs(Amount) WHERE Amount < 0

Thanks to all who replied - I appreciate the help!

"Lyle Fairfield" <MissingAddress@Invalid.Com> wrote in message
news:Xns945E9F08D2E7CFFDBA@130.133.1.4...[color=blue]
> "deko" <dje422@hotmail.com> wrote in news:ONjHb.2907$NJ7.1857
> @newssvr29.news.prodigy.com:
>[color=green]
> > Is there an easy way to make all negative values positive in a[/color][/color]
particular[color=blue][color=green]
> > table?
> >
> > I've been experimenting with this:
> >
> > Dim rst As DAO.Recordset
> > Set rst = db.OpenRecordset("tblNegValues")
> >
> > Do
> > If rst!Amount < 0 Then rst!Amount = rst!Amount - (rst!Amount * 2)
> > Loop
> >
> > Is there a better way?[/color]
>
> I suppose (whimsically), that you are storing a calculated field if you do
> this, and that you should leave your data as it is, and simply use and/or
> display the absolute value of these values.
>
>
> --
> Lyle
> (for e-mail refer to http://ffdba.com/contacts.htm)[/color]

hi
I just made a little query to do the job and this is the SQL code it
generated

UPDATE t_BANK_LINE SET t_BANK_LINE.BKL_EXPENSE = Abs([BKL_EXPENSE]);

it change all values of BKL_EXPENSE to positive . .

in code you could use

DoCmd.RunSql "UPDATE t_BANK_LINE SET t_BANK_LINE.BKL_EXPENSE =
Abs([BKL_EXPENSE]);"

there is no test for positive or negative numbers . . as the result is ALL
positive

cheers
paul g

"David W. Fenton" <dXXXfenton@bway.net.invalid> wrote in message
news:945E98BAFdfentonbwaynetinvali@24.168.128.78.. .[color=blue]
> dje422@hotmail.com (deko) wrote in
> <TzkHb.2919$ib.1595@newssvr29.news.prodigy.com>:
>[color=green]
> >I'll bet Abs is more efficient than multiplication and
> >subtraction, but I still need a way to apply this to an entire
> >table. Should I use For Each? Must I use a Recordset operation
> >here?[/color]
>
> If you're changing all the values in a column in a table, or a
> subset of values that match certain criteria, SQL is definitely the
> way to go. Arno has given you one such solution.
>
> A SQL UPDATE is almost always going to be faster than walking
> through a recordset and changing one row at a time, though there
> are some kinds of changes that can't really be done with SQL, so
> you sometimes do need to walk a recordset. But it's not nearly as
> often as many people think (especially people who come from an
> xBase environment).
>
> --
> David W. Fenton http://www.bway.net/~dfenton
> dfenton at bway dot net http://www.bway.net/~dfassoc[/color]

"Kinetic - Paul G" <kinetic@alwaysonline.net.au> wrote in
news:3fee7f8e@alwaysonline.net.au:
[color=blue]
> hi
> I just made a little query to do the job and this is the SQL
> code it generated
>
> UPDATE t_BANK_LINE SET t_BANK_LINE.BKL_EXPENSE =
> Abs([BKL_EXPENSE]);
>
> it change all values of BKL_EXPENSE to positive . .
>
> in code you could use
>
> DoCmd.RunSql "UPDATE t_BANK_LINE SET t_BANK_LINE.BKL_EXPENSE
> = Abs([BKL_EXPENSE]);"
>
> there is no test for positive or negative numbers . . as the
> result is ALL positive
>
> cheers
> paul g[/color]

This is true, however, it is bad practice to update a record that's
not changing. There may be locking issues, some rdbms may timestamp
the records, Etc.

There is also the issue of performance in large tables where
updating two million records to fix a problem with two hundred
records imposes a serious load on the server.

Just my two cents worth.

Bob Q

"Bob Quintal" <bquintal@generation.net> wrote in message
news:67cf2e286cd727aadc5dcf397d0414dd@news.teranew s.com...[color=blue]
> "Kinetic - Paul G" <kinetic@alwaysonline.net.au> wrote in
> news:3fee7f8e@alwaysonline.net.au:
>[color=green]
> > hi
> > I just made a little query to do the job and this is the SQL
> > code it generated
> >
> > UPDATE t_BANK_LINE SET t_BANK_LINE.BKL_EXPENSE =
> > Abs([BKL_EXPENSE]);
> >
> > it change all values of BKL_EXPENSE to positive . .
> >
> > in code you could use
> >
> > DoCmd.RunSql "UPDATE t_BANK_LINE SET t_BANK_LINE.BKL_EXPENSE
> > = Abs([BKL_EXPENSE]);"
> >
> > there is no test for positive or negative numbers . . as the
> > result is ALL positive
> >
> > cheers
> > paul g[/color]
>
> This is true, however, it is bad practice to update a record that's
> not changing. There may be locking issues, some rdbms may timestamp
> the records, Etc.
>
> There is also the issue of performance in large tables where
> updating two million records to fix a problem with two hundred
> records imposes a serious load on the server.
>
> Just my two cents worth.
>
> Bob Q[/color]

Good points Bob . .. I'll keep them in mind . . cheers paul g