Solved

Declare a variable as a percent in VB

Posted on 2004-09-22
11
747 Views
Last Modified: 2012-05-05
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...
0
Comment
Question by:Galisteo8
  • 5
  • 5
11 Comments
 
LVL 18

Expert Comment

by:Data-Man
ID: 12127349
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


0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 12127803
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
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12127872
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?
0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 12128538
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.
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12133939
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?
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 8

Author Comment

by:Galisteo8
ID: 12133950
That is, the data type for ProfitSplit in the SQL table....
0
 
LVL 50

Accepted Solution

by:
Steve Bink earned 50 total points
ID: 12135002
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".
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp?SD=GN&LN=EN-US&gssnb=1

-----------------------------------------------------------------------------------------------------------------
decimal and numeric
decimal

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

numeric

Functionally equivalent to decimal.

money and smallmoney
money

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.

smallmoney

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

Approximate Numerics
float

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.

real

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
datetime

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.

smalldatetime

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


0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12172332
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. :)
0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 12174194
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 ...
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12182700
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.
0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 12192786
Cool beans!  Good luck with the rest of your project.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now