467,202 Members | 1,027 Online

# 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? Nov 12 '05 #1
• viewed: 28283
Share:
12 Replies
 "deko" wrote in message news:ON*****************@newssvr29.news.prodigy.co m... 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? The Abs function? Nov 12 '05 #2
 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" wrote in message news:fb*****************@twister.nyroc.rr.com... "deko" wrote in message news:ON*****************@newssvr29.news.prodigy.co m... 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? The Abs function? Nov 12 '05 #3
 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******@pcdatasheet.com www.pcdatasheet.com "rkc" wrote in message news:fb*****************@twister.nyroc.rr.com... "deko" wrote in message news:ON*****************@newssvr29.news.prodigy.co m... 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? The Abs function? Nov 12 '05 #4
 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" schreef in bericht news:ON*****************@newssvr29.news.prodigy.co m... 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? Nov 12 '05 #5
 dj****@hotmail.com (deko) wrote in : I'll bet Abs is more efficient than multiplication andsubtraction, but I still need a way to apply this to an entiretable. Should I use For Each? Must I use a Recordset operationhere? 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 Nov 12 '05 #6
 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" wrote in message news:3f**********************@dreader2.news.tiscal i.nl... 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" schreef in bericht news:ON*****************@newssvr29.news.prodigy.co m... 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? Nov 12 '05 #7
 "deko" 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.OpenRecordset("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) Nov 12 '05 #8
 hi, update MyTable Set Amount=(Amount*-1) where Amount <0 "deko" schrieb im Newsbeitrag news:ON*****************@newssvr29.news.prodigy.co m... 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? Nov 12 '05 #9
 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" wrote in message news:Xn*******************@130.133.1.4... "deko" 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.OpenRecordset("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) Nov 12 '05 #10
 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" wrote in message news:94***************************@24.168.128.78.. . dj****@hotmail.com (deko) wrote in :I'll bet Abs is more efficient than multiplication andsubtraction, but I still need a way to apply this to an entiretable. Should I use For Each? Must I use a Recordset operationhere? 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 Nov 12 '05 #11
 "Kinetic - Paul G" wrote in news:3f******@alwaysonline.net.au: 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 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 Nov 12 '05 #12
 "Bob Quintal" wrote in message news:67******************************@news.teranew s.com... "Kinetic - Paul G" wrote in news:3f******@alwaysonline.net.au: 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 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 Good points Bob . .. I'll keep them in mind . . cheers paul g Nov 12 '05 #13

### This discussion thread is closed

Replies have been disabled for this discussion.