Conditional Input Mask (Access 2000,2002)

Topic

New Reply

Is it possible to set a conditional Input Mask
I need to store numbers that are stored as text in the following formats

If there are eleven digits
7 75867 87654

If there are twelve digits
9 87564 74658 0

if ther are thirteen digits
8 875645 576874

if there are fourteen digits
9 786777 687996 8

Entering the digits in these formats is easy enough, but you will have that person who will run them together.
Really messes up a barcode.

Reply | Quote

Replies

You can’t do this with an input mask; Access doesn’t support conditional input masks.
Instead, you can use in the After Update event of a text box bound to the field in a form. Take out all spaces, then put in spaces at the correct places for the length of the string. Say the text box is named txtField.

Private Sub txtField_AfterUpdate()
Dim strVal As String
strVal = Replace(Me.txtField, " ", "")
Select Case Len(strVal)
Case 11
strVal = Left(strVal, 1) & " " & Mid(strVal, 2, 5) & " " & Right(strVal, 5)
Case 12
strVal = Left(strVal, 1) & " " & Mid(strVal, 2, 5) & " " & Mid(strVal, 7, 5) & _
" " & Right(strVal, 1)
Case 13
strVal = Left(strVal, 1) & " " & Mid(strVal, 2, 6) & " " & Right(strVal, 6)
Case 14
strVal = Left(strVal, 1) & " " & Mid(strVal, 2, 6) & " " & Mid(strVal, 8, 6) & _
" " & Right(strVal, 1)
End Select
Me.txtField = strVal
End Sub

Private Sub txtField_BeforeUpdate(Cancel As Integer)
Dim strVal As String
strVal = Replace(txtField, " ", "")
If Len(strVal) 14 Then
MsgBox "Number of digits must be between 11 and 14.", vbExclamation
Cancel = True
End If
End Sub

The Before Update event is used to check the length of the input, and the After Update event to format the string.

Reply | Quote

Did not think that it was possible,
Thank you kindly for the code.

Reply | Quote