Limit variable to text/number

[Robert_L_Ross]

I have some code that asks the user for information on a template:
Dim ACCTSSN As Integer
ACCTSSN = InputBox("Enter Borrower's SSN (123456789)", "SSN")

I check the value to make sure they put in 9 characters:
If Len(Trim(ACCTSSN)) <> 9 Then
ERROR = MsgBox("SSN must be 9 digits", vbOKOnly, "SSN Error")
GoSub SUB_GET_ACCTSSN
End If

Now how do I make sure they don't put in text/special characters? I
initially had the field set as a string, but thought that making it an
integer would prevent the field from accepting letters. It does accept
letters and gives me an error when I click OK on the box.

I also have other input boxes that I'm going to want to check to see if the
user put in a valid date format (00/00/00 or 00/00/0000 or 0/0/0000).

How can I check a variable to see if it's got the right format, or limit the
variable so you can't put an invalid value in it while using an input box to
obtain the value?

THX!

 

[Jay Freedman]

There are a number of different ways to test input. Some are better for one
kind of input, some for others.

For the example you gave, use the Like operator to compare the input to a
string of 9 digits:

If Not ACCTSSN Like "#########" Then
MsgBox "SSN must be 9 digits", vbOKOnly, "SSN Error"
GoSub SUB_GET_ACCTSSN
End If

[Side note: As it says in the Help topic on GoSub, "Creating separate
procedures that you can call may provide a more structured alternative to
using GoSub...Return."]

There are also built-in functions such as IsNumeric and IsDate that you can
apply to any string -- look at the Help topics on those.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

 

[John Nurick]

Hi Robert,

This may help: http://www.mvps.org/access/modules/mdl0063.htm

 

[macropod]

Hi Robert,

You also need to:
Dim ACCTSSN As Long

Cheers

--
macropod
[MVP - Microsoft Word]


| I have some code that asks the user for information on a template:
| Dim ACCTSSN As Integer
| ACCTSSN = InputBox("Enter Borrower's SSN (123456789)", "SSN")
|
| I check the value to make sure they put in 9 characters:
| If Len(Trim(ACCTSSN)) <> 9 Then
| ERROR = MsgBox("SSN must be 9 digits", vbOKOnly, "SSN Error")
| GoSub SUB_GET_ACCTSSN
| End If
|
| Now how do I make sure they don't put in text/special characters? I
| initially had the field set as a string, but thought that making it an
| integer would prevent the field from accepting letters. It does accept
| letters and gives me an error when I click OK on the box.
|
| I also have other input boxes that I'm going to want to check to see if the
| user put in a valid date format (00/00/00 or 00/00/0000 or 0/0/0000).
|
| How can I check a variable to see if it's got the right format, or limit the
| variable so you can't put an invalid value in it while using an input box to
| obtain the value?
|
| THX!

 

[Steve Yandl]

Robert,

A slightly different approach would be to use a TextBox on a UserForm to
gather the input instead of an InputBox. The subroutine below is one I've
used for SSNs (it will only take digits or the dash symbol), nothing goes
into TextBox1 if the user presses anything that might not be part of the SSN
on the keyboard. You would still want to check the final result for proper
number of characters. I place the subroutine in the code for the UserForm
where TextBox1 resides.

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If KeyAscii < 48 And KeyAscii <> 45 Or KeyAscii > 57 Then KeyAscii = 0
End Sub

Steve Yandl