Allow a decimal place for  whole numbers

Fordraiders
Fordraiders used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2014
Commented:
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.

Author

Commented:
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 ?
Most Valuable Expert 2014

Commented:
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.

Author

Commented:
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...

Author

Commented:
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.
Most Valuable Expert 2014

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

Author

Commented:
thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial