update condition (Access 2000)

Topic

New Reply

I have a good function that updates the field EndPrice with the field grossprice I need however to improve the function and update only those products where
the size is less than 6 or < 6. .I have a field called size in the table products.I understand i have to insert also size in the select clause, but how can i formulate the condition ?
All the products with a size greater than 6 should have a 0 Value.

Below is my working function that needs to be changed with the aboce condition.

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT grossprice, endprice FROM products")
rs.MoveFirst
Do
rs.Edit
rs.Fields("endprice") = EndPrice(rs.Fields("grossprice"))
rs.Update
rs.MoveNext
Loop While Not rs.EOF

Reply | Quote

Replies

You can do it like this:

Set rs = CurrentDb.OpenRecordset(“SELECT grossprice, endprice FROM products WHERE size < 6")

if you want to update only records for which size < 6, as you state first. If, as you state later, you want to set endprice to 0 for records not satisying the condition, it is different:

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT grossprice, endprice, size FROM products")
rs.MoveFirst
Do
rs.Edit
If rs.Fields("size") < 6 Then
rs.Fields("endprice") = EndPrice(rs.Fields("grossprice"))
Else
rs.Fields("endprice") = 0
End If
rs.Update
rs.MoveNext
Loop While Not rs.EOF

Reply | Quote