CODEWITHSUNDEEP
vbams-accessms-access-2016
Tesel
Tesel

Reputation: 41

Multiply every record in Double column with a decimal number

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.

Upvotes: 0

Views: 50

Answers (2)

Gustav
Gustav

Reputation: 55951

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

Upvotes: 2

Scott Holtzman
Scott Holtzman

Reputation: 27259

While the user interface in access will use your local settings, VBA uses US settings only.

So you'll need to convert 0,5 to 0.5.

The below should work:

holder = Val(Replace(strDiscount,",","."))

Upvotes: 0

Related Questions