I have a column 'Price' of type Double. I also have a form with a button that should open an Inputbox to let the user enter the amount of discount they want, e.g. The user enters 0,6 (60% discount) and a calculation is done, 1 - 0,6 = 0,4. 0,4 is the number to multiply every record in the Price column with. I don't seem to get this to work.
One of my tries when entering 0,5 only multiplied by 5, it skipped the 0, before the 5. which was weird.
Dim strDiscount As String
Dim discount As Double
Dim holder As Double
Dim strSQL As String
Dim dbs As DAO.Database
Set dbs = CurrentDb
strDiscount = InputBox("Enter how much discount you want:", "Amount of discount")
holder = Val(strDiscount)
discount = (1-holder)
strSQL = "UPDATE Prislista SET Listpris = Listpris *" & _
discount
dbs.Execute strSQL
One of the tries gave an error messages telling me I have the wrong location set on my windows OS. Something about using comma instead of dot. Well, I have my windows and ms-access on swedish and it doesn't matter to me whether I should use comma or dot when entering the discount.
First, always store amounts and quantities as Currency if four decimals is adequate.
Next, use CCur to convert your localised text from InputBox to a number.
Third, use Str to convert a decimal to its neutral string expression.
Then:
Dim strDiscount As String
Dim discount As Currency
Dim holder As Currency
Dim strSQL As String
Dim dbs As DAO.Database
Set dbs = CurrentDb
strDiscount = InputBox("Enter how much discount you want:", "Amount of discount")
holder = CCur(strDiscount)
discount = (1 - holder)
strSQL = "UPDATE Prislista SET Listpris = Listpris * " & Str(discount) & ""
dbs.Execute strSQL
Example:
strDiscount = "0,6"
holder = CCur(strDiscount)
discount = 1 - holder
? Str(discount)
.4