troubleshooting Question

Allow a decimal place for whole numbers

Avatar of Fordraiders
FordraidersFlag for United States of America asked on
Microsoft Access
7 Comments1 Solution85 ViewsLast Modified:
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%
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
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

Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros