Len, Char, instr Not sure what to use or how to use it

[snodrift1]

I need to bee able to query out all entries that are not like the following format S01-19-01-3. Users are entering incorrect data such as So1-19-o1-3, S0119-01-3, S01-19-01-3. Users are supposed to enter the data with One letter, 3 dashes and 7 numbers. If they enter any other way I need to be able to identify it with out searching through some 4000 records. Please help

 

[RuralGuy]

Put these two functions in your form's class module:

Function Validate(InString As String) As Boolean
'-- Checks that InString is in the format A99-99-99-9
Dim i As Integer
Validate = False  '-- Default to bad validation
If Len(InString) = 11 Then
   For i = 1 To 11
      Validate = ValidatePosition(Mid(InString, i, 1), i)
      If Not Validate Then
         Exit For
      End If
   Next i
End If

End Function

Function ValidatePosition(InChar As String, InPosition As Integer) As Boolean
'-- Positional Validation to be used with the Validate() function.
   ValidatePosition = False   '-- Initialize as failure
   Select Case InPosition
      Case 1
         If UCase(InChar) >= "A" And UCase(InChar) <= "Z" Then
            ValidatePosition = True
         End If
      Case 4, 7, 10
         If InChar = "-" Then
            ValidatePosition = True
         End If
      Case Else
         If InChar >= "0" And InChar <= "9" Then
            ValidatePosition = True
         End If
   End Select
End Function

...then put:
If Not Validate(Me.YourControlName) Then
MsgBox "Use the format A99-99-99-9 for your entry"
Cancel = True
End If
...in the BeforeUpdate event of your control. Using *your* control name of course. You would have to modify the code to check for UPPERCASE alpha at the beginning.

 

[snodrift1]

This does not seem to be working for me. I am needing to put this into the Field of my Query in Access. When I try using your example I am getting errors

 

[RuralGuy]

Then put the functions in a standard module instead. You can then call it from your query. You are still going to want to put this in the BeforeUpdate event of the control that takes the data input so you can catch the errors as they are made and let the user correct them. It will work on your form even when the code is in a standard module.