Solved

Declare a variable as a percent in VB

Posted on 2004-09-22
11
763 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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
 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

739 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