how to validate this string....

  • Thread starter Thread starter Paul Mak
  • Start date Start date
P

Paul Mak

I have a text box in a form, user type in the text and a query use the value
in the string to return some records from a table. The string has to be in
the following format to order to pass the parameters to the query:

FirstName LastName, Department

How to validate the string to that format?

eg
John Doe, IS.......pass

John Doe IS............fail

JohnDoe.................fail

John A Doe, IS......fail

J D, IS..................pass

JD, IS..................fail

Thanks
 
Hi,
From your description, the string should have one comma and two
spaces, so:

If InStr(1,yourTextBox,",",vbTextCompare) = 1 _
AND CountChars(yourTextBox," ") = 2 Then
'string is okay
Else
'string fails
End If



put the function below in a standard module

Public Function CountChar(strIn As String, CharToCount As String) As Integer
Dim i As Integer
Dim intPointer As Integer
intPointer = 1
Do While i <= Len(strIn)
If InStr(intPointer, strIn, CharToCount, vbTextCompare) <> 0 Then
i = i + 1
intPointer = InStr(intPointer, strIn, CharToCount, vbTextCompare) + 1
Else
Exit Do
End If
Loop

CountChar = i

End Function

Hopefully this will give you some ideas.
 
On second thought, why don't you just use
3 text boxes, one each for FirstName LastName and Department.
Concatenate them and insert the comma yourself.
That way there's no room for error.
 
You don't want to check for Instr()=1: that'll only find a comma in position
one of the string!

Oops, my bad!
 
Back
Top