How to make all negative values positive?

[deko]

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.OpenRecordse t("tblNegValues ")

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

Is there a better way?

[rkc]

"deko" <dj****@hotmail .com> wrote in message
news:ON******** *********@newss vr29.news.prodi gy.com...

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.OpenRecordse t("tblNegValues ")

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

Is there a better way?

The Abs function?

[deko]

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" <rk*@yabba.dabb a.do.rochester. rr.nope> wrote in message
news:fb******** *********@twist er.nyroc.rr.com ...

"deko" <dj****@hotmail .com> wrote in message
news:ON******** *********@newss vr29.news.prodi gy.com...

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.OpenRecordse t("tblNegValues ")

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

Is there a better way?

The Abs function?

[PC Datasheet]

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
re******@pcdata sheet.com
www.pcdatasheet.com

"rkc" <rk*@yabba.dabb a.do.rochester. rr.nope> wrote in message
news:fb******** *********@twist er.nyroc.rr.com ...

"deko" <dj****@hotmail .com> wrote in message
news:ON******** *********@newss vr29.news.prodi gy.com...

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.OpenRecordse t("tblNegValues ")

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

Is there a better way?

The Abs function?

[Arno R]

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

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

--
Hope this helps
Arno R

"deko" <dj****@hotmail .com> schreef in bericht
news:ON******** *********@newss vr29.news.prodi gy.com...

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.OpenRecordse t("tblNegValues ")

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

Is there a better way?

[David W. Fenton]

dj****@hotmail. com (deko) wrote in
<Tz************ ****@newssvr29. news.prodigy.co m>:

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?

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

[deko]

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" <ar************ ****@tiscali.nl > wrote in message
news:3f******** **************@ dreader2.news.t iscali.nl...

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

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

--
Hope this helps
Arno R

"deko" <dj****@hotmail .com> schreef in bericht
news:ON******** *********@newss vr29.news.prodi gy.com...

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.OpenRecordse t("tblNegValues ")

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

Is there a better way?

[Lyle Fairfield]

"deko" <dj****@hotmail .com> wrote in news:ONjHb.2907 $NJ7.1857
@newssvr29.news .prodigy.com:

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.OpenRecordse t("tblNegValues ")

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

Is there a better way?

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)

[Werner Ihmann]

hi,

update MyTable
Set Amount=(Amount*-1)
where Amount <0
"deko" <dj****@hotmail .com> schrieb im Newsbeitrag
news:ON******** *********@newss vr29.news.prodi gy.com...

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.OpenRecordse t("tblNegValues ")

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

Is there a better way?

[deko]

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" <Mi************ @Invalid.Com> wrote in message
news:Xn******** ***********@130 .133.1.4...

"deko" <dj****@hotmail .com> wrote in news:ONjHb.2907 $NJ7.1857
@newssvr29.news .prodigy.com:

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.OpenRecordse t("tblNegValues ")

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

Is there a better way?

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)