Text Box - only allow numeric values

Question

Hi,

I am using Microsoft Access for Microsoft 365 MSO (16.0.14326.20164) 64 bit.

I have an unbound Text Box named txtQuantity on a Form. I would like to allow only numeric values to be entered into this Text Box. I thought I might be able to accomplish this by setting the "Validation Rule" property of this textbox to either of the following...

IsNumeric ([txtQuantity] )

IsNumeric([txtQuantity].[Text])

... Neither of these is working for me. With both settings, my validation rule gets triggered any time I try to move the focus off the txtQuantity field, regardless of whether I have a numeric or non-numeric value entered.

If anyone here can suggest a better way that I can allow only numeric values to be entered, I would greatly appreciate it.

Thanks and best regards,
Paul

Answer 1

Put the following in the control's KeyPress event procedure:

    Select Case KeyAscii

        Case 48 To 57

        ' numeric character, do nothing

        Case 8

        ' backspace, do nothing

        Case Else

        ' suppress character entered

        KeyAscii = 0

    End Select

I you want to allow other special characters, such as a comma as the thousands separator or arithmetical symbols, then you'd need to augment the code accordingly.  To find the ASCII value for a character call the Asc function in the immediate window, e.g. to find the ASCII value for the comma character:

? Asc(",")

 44

Answer 2

Hi Paul, I'll try to help.

Have you looked at seting an input mask? With an input mask, you can restrict the type of characters entered. This article details how to use input masks:

https://support.microsoft.com/en-us/office/cont....

If you enter an input mask of 9, the user can only type the digits 0-9.

Answer 3

You are asking a question: Is this a number value, or not? Answer Yes or No.

That's not going have the effect of forcing the field to accept only Numeric values, it's only going to tell you what kind of value you entered, a number or not a number.

You need to change the data type of the field from text to Number, either Integer or Long Integer I suspect. That's assuming you are not collecting digital strings like telephone numbers. If you are collecting digital strings, then you might consider an Input Mask rather than a validation rule.

Answer 4

Thank you Scott, Ken, and George -

I think all of your suggestions could have worked for me. It turned out that what was initially making EVERYTHING I tried not work was that I stupidly had the "Text Format" property set to "Rich Text" instead of "Plain Text". With this setting, when I typed a number (for example 3) into the field, it was actually getting surrounded by <div></div> tags, so when looked to me like I had typed in 3, the actual text in the TextBox was <div>3</div>.

After correcting this, setting to the "Format" property to "General Number" accomplished my objective. Thank you all for your help!

Best regards,
Paul