Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

field to allow only numerics with one exception

Posted on 2016-08-30
13
Medium Priority
?
64 Views
Last Modified: 2016-08-30
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
Comment
Question by:fordraiders
[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
  • 3
  • 3
  • +1
13 Comments
 
LVL 75
ID: 41776808
The Plus and Minus signs are considered 'numeric' ... if at the beginning of a number.

-2010201
+192818
0
 
LVL 26

Expert Comment

by:Nick67
ID: 41776811
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
 
LVL 3

Author Comment

by:fordraiders
ID: 41776823
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 75
ID: 41776832
-
0
 
LVL 39

Expert Comment

by:PatHartman
ID: 41776838
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
 
LVL 26

Accepted Solution

by:
Nick67 earned 2000 total points
ID: 41776839
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
 
LVL 39

Expert Comment

by:PatHartman
ID: 41776858
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
 
LVL 3

Author Comment

by:fordraiders
ID: 41776861
nick67, Worked great !  I also added  "ASCII 46" for .   decimal

thanks

now i got another problem , but another question.

thanks !!
0
 
LVL 3

Author Closing Comment

by:fordraiders
ID: 41776883
Thanks...
worked best
0
 
LVL 39

Expert Comment

by:PatHartman
ID: 41776911
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
 
LVL 3

Author Comment

by:fordraiders
ID: 41776920
Pat, I hope to pray folks don't enter that garbage, but you are right, you never know.
Thanks.
0
 
LVL 3

Author Comment

by:fordraiders
ID: 41776931
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
 
LVL 26

Expert Comment

by:Nick67
ID: 41776943
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

636 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