access 97: function for multiplying numbers

[JMCN]

Hello -
This should be an easy function to multiple all amounts if they
are negative values (< 0). however, i get an error message on the
db.openrecordset because it assumes that i want to count the number
of records that i have in the database. i just want to multiple the
myvalue by -1. what am i missing or doing wrong?
thanks in advance.

Dim db As Databases
Dim rst As Recordset
Dim I As Integer
Dim x As String
Dim MyValue

Set db = CurrentDb
'Set rst = db.OpenRecordset("tblBMOD-test")
MyValue = [Amount]

rst.MoveFirst
x = 0
Do Until rst.EOF
'multiply if MyValue is less than zero until all [ET Amount]
records are positive
'Integers need to be positive values.

If MyValue > 0 Then
MyValue = MyValue * -1
End If
rst.MoveNext
Loop

MsgBox "Finished!! All amounts are positive values.", vbOKOnly

[Peter Doering]

On 28 Nov 2003 12:44:26 -0800, JMCN wrote:

This should be an easy function to multiple all amounts if they
are negative values (< 0). however, i get an error message on the
db.openrecordset because it assumes that i want to count the number
of records that i have in the database. i just want to multiple the
myvalue by -1. what am i missing or doing wrong?
thanks in advance.

Dim db As Databases
Dim db As Database 'remove the "s"
Dim rst As Recordset
Dim I As Integer
Dim x As String
Dim MyValue

Set db = CurrentDb
'Set rst = db.OpenRecordset("tblBMOD-test")
Set rst = db.OpenRecordset("tblBMOD-test", dbOpenDynaset)
(Quote removed, dynaset to enable updates).
MyValue = [Amount]
This line is useless here. See below.

rst.MoveFirst
x = 0
Do Until rst.EOF
'multiply if MyValue is less than zero until all [ET Amount]
records are positive
'Integers need to be positive values.

MyValue = [Amount] 'Inserted from above
If MyValue > 0 Then
If MyValue < 0 Then
MyValue = MyValue * -1
With rst
.Edit
![Amount] = MyValue
.Update
End With
End If
rst.MoveNext
Loop
rst.Close
Set rst = Nothing

MsgBox "Finished!! All amounts are positive values.", vbOKOnly

It would be faster this way:

Dim strSelect As String
strSelect = "UPDATE [tblBMOD-test] " & _
" SET [Amount]=[Amount]*-1 "& _
" WHERE [Amount]<0;"
DoCmd.SetWarnings False
DoCmd.RunSQL strSelect
DoCmd.SetWarnings True
HTH - Peter

--
No mails please.

[Michael Hopwood]

In addition to Peters response, you should look at the function Abs() which
returns the absolute value of a number so -5 becomes 5 etc.

--
Michael Hopwood (Phobos)
"JMCN" <pi******@yahoo.fr> wrote in message
news:27**************************@posting.google.c om...

Hello -
This should be an easy function to multiple all amounts if they
are negative values (< 0). however, i get an error message on the
db.openrecordset because it assumes that i want to count the number
of records that i have in the database. i just want to multiple the
myvalue by -1. what am i missing or doing wrong?
thanks in advance.

Dim db As Databases
Dim rst As Recordset
Dim I As Integer
Dim x As String
Dim MyValue

Set db = CurrentDb
'Set rst = db.OpenRecordset("tblBMOD-test")
MyValue = [Amount]

rst.MoveFirst
x = 0
Do Until rst.EOF
'multiply if MyValue is less than zero until all [ET Amount]
records are positive
'Integers need to be positive values.

If MyValue > 0 Then
MyValue = MyValue * -1
End If
rst.MoveNext
Loop

MsgBox "Finished!! All amounts are positive values.", vbOKOnly

[David W. Fenton]

no****@doering.org (Peter Doering) wrote in
<bq*************@ID-204768.news.uni-berlin.de>:

It would be faster this way:

Dim strSelect As String
strSelect = "UPDATE [tblBMOD-test] " & _
" SET [Amount]=[Amount]*-1 "& _
" WHERE [Amount]<0;"
DoCmd.SetWarnings False
DoCmd.RunSQL strSelect
DoCmd.SetWarnings True

If you're going to do it in code, then:

Dim db As Database

Set db = CurrentDB()
strSQL = [your SQL string]
db.Execute strSQL, dbFailOnError
Set db = Nothing

That way, you don't have to worry about SetWarnings.

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

[JMCN]

"Michael Hopwood" <la******@spammers.co.uk> wrote in message news:<u8********************@brightview.com>...

In addition to Peters response, you should look at the function Abs() which
returns the absolute value of a number so -5 becomes 5 etc.

--
Michael Hopwood (Phobos)

thank you all for your advice! i forgot about the absolute function,
actually i have never used it and it worked well!!
thanks again! jung