• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 800
  • Last Modified:

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...
  • 5
  • 5
1 Solution
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


Steve BinkCommented:
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
Galisteo8Author Commented:
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?
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Steve BinkCommented:
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.
Galisteo8Author Commented:
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?
Galisteo8Author Commented:
That is, the data type for ProfitSplit in the SQL table....
Steve BinkCommented:
There are a number of data types in MSSQL capable of handling an Access single or variant/decimal data type.  Below is a listing from the SQL "Books Online" for applicable types.  Note that decimal is a valid type for MSSQL, but not for Access.  All of these types differ slightly from one another, mostly in respect to precision (number of placeholders overall), scale(number of decimal places), or length (storage size).  Several of the data types also come with additional properties, such as the datetime or money data types, which would not make them ideal for your uses.  The generic non-integer data type is float, and it should be able to handle just about anything you throw at it.

In case you would like a copy of the Books Online for MSSQL, here's a link for you.  I would recommend looking up "Data Types", "Precision, Scale, and Length", and "Data Type Precedence".

decimal and numeric

Fixed precision and scale numeric data from -10^38 +1 through 10^38 –1.


Functionally equivalent to decimal.

money and smallmoney

Monetary data values from -2^63 (-922,337,203,685,477.5808) through 2^63 - 1 (+922,337,203,685,477.5807), with accuracy to a ten-thousandth of a monetary unit.


Monetary data values from -214,748.3648 through +214,748.3647, with accuracy to a ten-thousandth of a monetary unit.

Approximate Numerics

Floating precision number data with the following valid values: -1.79E + 308 through -2.23E - 308, 0 and 2.23E + 308 through 1.79E + 308.


Floating precision number data with the following valid values: -3.40E + 38 through -1.18E - 38, 0 and 1.18E - 38 through 3.40E + 38.

datetime and smalldatetime

Date and time data from January 1, 1753, through December 31, 9999, with an accuracy of three-hundredths of a second, or 3.33 milliseconds.


Date and time data from January 1, 1900, through June 6, 2079, with an accuracy of one minute.

Galisteo8Author Commented:
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
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. :)
Steve BinkCommented:
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 ...


If instr([Forms]![ClientForm]![ProfitSplit].Text,"%") = 0 Then ...
Galisteo8Author Commented:
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.
Steve BinkCommented:
Cool beans!  Good luck with the rest of your project.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now