Link to home
Start Free TrialLog in
Avatar of Galisteo8
Galisteo8

asked on

Declare a variable as a percent in VB

I have an access form where the user enters, say, "25" into a control called ProfitSplit, and then I want the value to be displayed as "25%" and stored in the underlying SQL table as 0.25.

The Data Type of the column in the SQL table is Decimal.
The Format of the control on the Access form is General Number, with 2 decimal places showing.

I have an AfterUpdate event on the control which is as follows:

Private Sub ProfitSplit_AfterUpdate()
Dim percent As float
percent = 0
percent = [Forms]![Clientform]![ProfitSplit] / 100
[Forms]![Clientform]![ProfitSplit] = percent
End Sub

However, the result is that ProfitSplit is always turned into a 0.  The problem occurs in the line  " percent = [Forms]![Clientform]![ProfitSplit] / 100 "  -- basically, percent is not getting the value properly assigned to it.  I do not know why...
Avatar of Data-Man
Data-Man
Flag of United States of America image

you should prefix follow naming conventions with variables..I think percent is a key word.  Also, why do you want to do this.

Just set the format property of the field to percent

Mike


Avatar of Steve Bink
Also, make sure your [ProfitSplit] field is not an integer data type.  When an integer variable or field receives a float value, VB tries to implicitly convert the value, usually dropping the decimal portion.  As an example, try running this code, then look at the output in the debug window.

Public Sub ShowImplicitConversion()
Dim x As Integer
Dim y As Single

y = 3.12
x = y
Debug.Print y, x

End Sub
Avatar of Galisteo8
Galisteo8

ASKER

If I simply set the control's format property to Percent, then the user's entry is multiplied by 100, i.e. 25 becomes 2500.

The Data Type for ProfitSplit is Decimal.


Noob question: How do I "just run" the code that you suggest, routinet?
Copy the code from the word "Public" to the line "End Sub" and paste it into any module in your database.  If your database does have a module (as opposed to form, table, etc.), create a blank one.  Once the code is in the module, put the cursor anywhere inside the sub, and press F5.  Make sure you have your debug window open by pressing Ctrl-G, or selecting [ View | Immediate Window ] from the menu bar.

Also, VBA does not have a strict Decimal data type.  A variable or field identified as Decimal will actually be a Variant type implicitly converted to a Decimal sub-type.  This could be one source of your problem.  Try changing the data type to Single, and use the field properties to restrict precision.
And what about the underlying column in the SQL table - what data type should I use there? I can't use Int, obviously... What about float?
That is, the data type for ProfitSplit in the SQL table....
ASKER CERTIFIED SOLUTION
Avatar of Steve Bink
Steve Bink
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Problem solved!
For anyone looking for the solution, here it is:

In the SQL table---------------------------
The column (in my case, called ProfitSplit) is configured with a Data Type of 'decimal'. Precision is set to 6 and Scale is set to 3 -- this way the valid range of values goes from 000.000 to 100.000.

On the Access Form----------------------------------------
The ProfitSplit control's Format property is set to 'Percent', with the Decimal Places property set to 2. This control is bound (via Control Source property) to the ProfitSplit column of the underlying SQL table. The control's AfterUpdate event procedure is then set as follows:

Private Sub ProfitSplit_AfterUpdate()
Dim Perc As Single
Perc = 0
Perc = [Forms]![Clientform]![ProfitSplit] / 100
[Forms]![Clientform]![ProfitSplit] = Perc
[Forms]![Clientform].Requery
End Sub

When, say '25.8' is entered into the ProfitSplit control, that value is divided by 100 and then reassigned to the control by way of the Perc variable in the AfterUpdate event, producing and displaying the new value '25.8%'. The SQL table holds this value as .258.

Note: My original problem was that, in the SQL column, the Scale was set to 0 by default when Decimal was selected as the data type. Having a Scale of 0 in essence rounds numbers to the nearest whole integer, and this prevents actual decimals from being entered via the Access form. To ensure that the decimal places entered via Access are legit values for the SQL column, set the Scale to the number of decimal places that the SQL column will need to recognize.

Second Note: When I tried 'float' as the column's data type, the resulting iterations(?) tended to cause some kind of overrun and shut down Access.

Thanks to routinet for helping guide me in the right direction. Was a fairly simple solution when I realized how Precision and Scale worked together. :)
Glad you got it working!  The only problem I see with your solution is the possibility of a duplicate operation.  For example, say you enter a record with "25.8" in the [ProfitSplit] box.  Access will update this, through your code, to ".258", or "25.8%".  You save the record.  Now you have to go back and change a phone number or other miscellaneous data.  When you save the record again, Access MIGHT run your code again, leaving you with ".00258" in your [ProfitSplit] box.  This will probably only occur if someone changes what is in the box to begin with, since you used the OnAfterUpdate event for the control itself, but you will probably want to check for that possibility in your code.  Experiment with the .Text and .Value properties of the control...perhaps one of them (Text, most likely) will return the "%" character in the property, indicating the conversion has already been done.  If you have a minimum value for the field, you can check against that, too.

If [Forms]![ClientForm]![ProfitSplit] > .01 Then ...

OR

If instr([Forms]![ClientForm]![ProfitSplit].Text,"%") = 0 Then ...
Routinet, thanks for the heads up. I've tested the form by updating various other control values on it, and there appears to be no undue re-calc of the ProfitSplit control whenever the form -- as a whole -- gets updated.

I will continue to monitor as the project proceeds, just to be sure.
Cool beans!  Good luck with the rest of your project.