Reduce/Increase selected values by x%

D

David M C

I have a select query that selects the values I want to update.

LocationID (where LocationID = SomeControlOnForm)
TaskID
LabourPrice (where labour price is > 99)

I would like to turn this into an update query that raises/lowers the
LabourPrice by x%. So, if 100% is entered, LabourPrice stays the same etc
(basically, divide the entered value by 100 and multiply LabourPrice by this
value).

How can I do this? And how can I make it run from a button click on a form?

Thanks,

Dave
 
J

John Spencer

WARNING: Test this out on a copy of your database. You cannot "Undo" this
action

The query should look something like

UPDATE TheTable
SET LabourPrice = [LabourPrice] * 100 /[
Forms]![YourFormName]![YourControlName]
WHERE LocationID = [Forms]![YourFormName]![YourLocationControlName]
And LabourPrice > 99

The code for the button would look something like

If Me.YourControlName > 0 And _
Me.YourControlName < 200 And _
IsNull(Me.YourLocationID) = False Then
Currentdb().Execute "NameOfTheQuery"
End If

Note that this code has no error-handling and the range specified as
acceptable may not be what you want.
 
D

David M C

Thanks. I'd already managed to work out the query, even got it to round to
the nearest 5. Just wasn't sure how to get the query to execute from a
command button. Now I know.

Thanks,

Dave

John Spencer said:
WARNING: Test this out on a copy of your database. You cannot "Undo" this
action

The query should look something like

UPDATE TheTable
SET LabourPrice = [LabourPrice] * 100 /[
Forms]![YourFormName]![YourControlName]
WHERE LocationID = [Forms]![YourFormName]![YourLocationControlName]
And LabourPrice > 99

The code for the button would look something like

If Me.YourControlName > 0 And _
Me.YourControlName < 200 And _
IsNull(Me.YourLocationID) = False Then
Currentdb().Execute "NameOfTheQuery"
End If

Note that this code has no error-handling and the range specified as
acceptable may not be what you want.

David M C said:
I have a select query that selects the values I want to update.

LocationID (where LocationID = SomeControlOnForm)
TaskID
LabourPrice (where labour price is > 99)

I would like to turn this into an update query that raises/lowers the
LabourPrice by x%. So, if 100% is entered, LabourPrice stays the same etc
(basically, divide the entered value by 100 and multiply LabourPrice by
this
value).

How can I do this? And how can I make it run from a button click on a
form?

Thanks,

Dave
Click to expand...
Click to expand...
 
Top