• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 88
  • Last Modified:

field to allow only numerics with one exception

Access 2010 vba  keypress event

I have an unbound textbox.
That only allows numerics.

I need to allow only one other specific characters  -  negative sign ?

Thanks
fordraiders


If (KeyAscii > 47 And KeyAscii < 58) Or (KeyAscii = 8) Then
      KeyAscii = KeyAscii
      Else:
      MsgBox ("You Must Enter Numbers Only!")
      KeyAscii = 0
End If

Open in new window

0
Fordraiders
Asked:
Fordraiders
  • 5
  • 3
  • 3
  • +1
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
The Plus and Minus signs are considered 'numeric' ... if at the beginning of a number.

-2010201
+192818
0
 
Nick67Commented:
45 is the Ascii dash.
43 is the the Ascii +
I take it your code is a keypress event?
You aren't testing for isnumeric() so I assume the Change() event isn't in play
0
 
FordraidersAuthor Commented:
if i physically type in a negative or  plus sign...i get the message box..(which most people will do.  

Now if i copy and paste it will accept
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
-
0
 
PatHartmanCommented:
How are you restricting the textbox to allow only numbers?  It is better to use a numeric data type if these are truly numeric values.  As Joe already pointed out - the minus and plus signs are valid as the first character in numeric fields.
0
 
Nick67Commented:
Select Case True 'always your best friend
    Case (KeyAscii > 47 And KeyAscii < 58)
    Case (KeyAscii = 8)
    Case (KeyAscii = 43)
    Case (KeyAscii = 45)
    Case Else
        MsgBox ("You Must Enter Numbers Only!")
        KeyAscii = 0
        Exit sub
End select
KeyAscii = KeyAscii
0
 
PatHartmanCommented:
I just noticed that these also return true so if you want to restrict them to the first position, you will need to do that with code.

print isnumeric(+12-3)
True
print isnumeric(-12+3)
True

In all my years of using Access no user has ever put the plus or minus in the middle of the number.  It looks like Access goes along with the joke and adds or subtracts rather than balking.
0
 
FordraidersAuthor Commented:
nick67, Worked great !  I also added  "ASCII 46" for .   decimal

thanks

now i got another problem , but another question.

thanks !!
0
 
FordraidersAuthor Commented:
Thanks...
worked best
0
 
PatHartmanCommented:
Since I showed that Access is OK with multiple + and - in a numeric field, you might want to modify the code to count instances.  Access even allows this:

print isnumeric(-12.1+3.1)
True
0
 
FordraidersAuthor Commented:
Pat, I hope to pray folks don't enter that garbage, but you are right, you never know.
Thanks.
0
 
FordraidersAuthor Commented:
ok, Pat, I thought , I thought about it. Yep, I'm dealing with Sellers, copying and pasting is a concern now that i think about it. They copy from excel sheets all day...hmmmm.
0
 
Nick67Commented:
That's why I asked about the Change event.
What you seem to have is a KeyPress event that looks at the fitness of any one keypress.

A Change event will look at the fitness of the presently-entered value.
They can be quite persnicketty, though.
You have to think of ALL the ways the user may use the box (like rubbing out previous values)
0
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

  • 5
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now