Improve company productivity with a Business Account.Sign Up

x
?
Solved

Allow a decimal place for  whole numbers

Posted on 2016-08-30
7
Medium Priority
?
53 Views
Last Modified: 2016-08-31
I had this question after viewing field to allow only numerics with one exception.

Access 2010 vba  keypress event and after update event

I have an unBound Textbox that allows only numeric characters, but also negative sign, plus sign, and a decimal.

The goal is that no matter what type of number is entered it keeps decimals and then tags a % sign at the end.

What I need:
If a person happens to enter  25%  or just 25
I need the result to add .00 for whole numbers
25%  becomes  25.00%
and
25 becomes  25.00%

Right now if i enter  22.22 it gives me correctly  22.22%
If i enter  22.00  it takes away the   .00   to 22%
I need it to keep 22.00 if its entered
AND ALSO
22% becomes  22.00 %

Current Code:
Private Sub ROLLING_12_GP_AfterUpdate()

' if they backspace and leave "" or null thats ok
If Len(Nz(Me.ROLLING_12_GP, "")) = 0 Then
   Exit Sub
End If
' temp take out the % (keypress does not allow) if they enter it
Me!ROLLING_12_GP = Replace([ROLLING_12_GP], "%", "")
' now add it back for
Me!ROLLING_12_GP = Me.ROLLING_12_GP.Value / 100 * 100 & "%"

' delete the % if they leave it
If Me!ROLLING_12_GP = "%" Then
   Me!ROLLING_12_GPP = ""
End If
End Sub


Private Sub ROLLING_12_GP_KeyPress(KeyAscii As Integer)
Select Case True 'always your best friend
    Case (KeyAscii > 47 And KeyAscii < 58)
    Case (KeyAscii = 8)
    Case (KeyAscii = 43)
    Case (KeyAscii = 45)
    Case (KeyAscii = 46)
    Case Else
        MsgBox ("You Must Enter Numbers Only!")
        KeyAscii = 0
        Exit Sub
End Select
KeyAscii = KeyAscii
End Sub

Open in new window



Thanks
fordraiders
0
Comment
Question by:Fordraiders
  • 4
  • 3
7 Comments
 
LVL 26

Accepted Solution

by:
Nick67 earned 2000 total points
ID: 41776998
Leave the % sign off
Format it with a custom format in the AfterUpdate

If IsNumeric(Me.ROLLING_12_GP) = True Then
    Me.ROLLING_12_GP.Value = Format(Me.ROLLING_12_GP.Value / 100, "0.00%")
End If

Nicely enough, this kills the math thing @PatHartman noted
Try 12.3-6.4
It doesn't format, so you can msgbox about invalid data then.
0
 
LVL 3

Author Comment

by:Fordraiders
ID: 41777006
I'l take the code.
I took a simplier route.
Formatted the unbound textbox as "Percent"  which adds my % automatically.
"Decimals" to 2 places, which keeps my whole numbers !

Unless i'm not thinking it through ?
0
 
LVL 26

Expert Comment

by:Nick67
ID: 41777012
I thought about suggesting that.
It can depend on what the users enter

With textbox formatting '25' becomes '2500.00%'
That can be annoying.
If you are looking at percents and entering them, not needing to do the head-math, but straight entry is nice.
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
LVL 3

Author Comment

by:Fordraiders
ID: 41777027
but that does not work for my subform with fields that are bound ...uggggh !
the data type changes that is...so now i'm back to code on the subform.
these field are text...
0
 
LVL 3

Author Comment

by:Fordraiders
ID: 41777040
welll kudos to you and Pat!!. Should have given Pat some Points.
But your above code worked great !!
in the subform !!.

Thanks
Nick !  and Pat.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 41777061
<grin>
What you display and what you store do not have to be, and generally are not the same thing, my son.

Save as a single, decimal or fixed or text but display as a percentage -- as you can see alters stuff.
Store '25' and display it as 25.00%
or store '25.00%' and display it as-is.

A lot of folks get unhappy when they ask questions about UI and get answers about the data design -- but data design is the arse-biter.

Other ways to play is to put a transparent control with one on top of the other.
One control displays niceness while the other actually gets the focus and permits entry and editing handled by GotFocus/LostFocus events.

I don't know that @PatHartman looked at this one.
You can go back to the other one and 'Request Attention' and ask to re-open the Q to re-assign pointsRA
0
 
LVL 3

Author Closing Comment

by:Fordraiders
ID: 41778049
thanks
0

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

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

Implementing simple internal controls in the Microsoft Access application.
If you need to implement application level security in an Access database application or other VBA code, I strongly encourage you to take advantage of Active Directory groups.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

580 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