Thread: Tricky Data Format Validation
11-07-09, 11:46 #1Registered User
- Join Date
- Aug 2004
Unanswered: Tricky Data Format Validation
I have a text field on a form - the allowable formats are as follows:
v99.99, 999.99, 999, v99, 999.9, or v99.9
how can I check to see if the data is entered to match one of the formats above?
the v is always a v (if it exists) - the numeric portion can be any numbers... ex: v10.21 123.100, 359, v25, 350.2, v12.6 etc
11-07-09, 13:43 #2Registered User
- Join Date
- Oct 2009
well even in your example of 123.100 : that is not allowable per your own spec...
it might be messy. a big string of text layout testing with alot of if/thens.....in VB help you can check out the methods of Find, Left, Right and Count - you can find the . and then count to the left or to the right...won't be too bad to permit 3 spaces to left of . and two spaces to right of .
but it will be messy, though do-able, if you have check for v or other letters rather than numbers...
I don't think one can have an OR statement inside the mask property....
you might be better off determining what is most probable that the user would do wrong and testing for that instead.....
but one thing that is easy to do is just define the field property of the underlying table to have only 6 characters - that is an easy way to limit the outter boundry of character entry.
Last edited by NTC; 11-07-09 at 13:48.
11-07-09, 22:01 #3Moderator
- Join Date
- Dec 2004
- Madison, WI
Otherwise one way (although someone can probably shorten this syntax or come up with something better) is something like this in the AfterUpdate event of the field:
If not instr([MyFieldName],"v") and not instr([MyFieldName],"0") and not instr([MyFieldName],"1") and not instr([MyFieldName],"2") and not instr([MyFieldName],"3") and not instr([MyFieldName],"4") and not instr([MyFieldName],"5") and not instr([MyFieldName],"6") and not not instr([MyFieldName],"7") and not instr([MyFieldName],"8") and not instr([MyFieldName],"9") and not instr([MyFieldName],".") then
msgbox "Incorrect digit used."
me!MyFieldName = null
or you could also just test the first left character and use the isnumeric function:
if isnumeric(left([MyFieldName],1)) = false and left([MyFieldName],1) <> "v" then
msgbox "Not correct starting digit.
Last edited by pkstormy; 11-07-09 at 22:05.Expert Database Programming
MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)
11-08-09, 05:36 #4Registered User
- Join Date
- Jun 2007
- Maitland NSW,Australia
You could try the following example.
test_data is your field name, but I set it as a variable for this example.
search_string is the valid characters that can be entered
This code steps through the test_data and checks if each character is in the search_string. If the character is not in the search_string then it displays a message exits the sub.
If you change test data to "b123.00" then the invalid number message box is displayed.
Dim field_length As Integer
Dim string_result As Integer
Dim string_counter As Integer
Dim test_data As String
Dim search_string As String
test_data = "v123.00"
search_string = "v.0123456789"
field_length = Len(test_data)
For string_counter = 1 To field_length
string_result = InStr(search_string, Mid(test_data, string_counter, 1))
If string_result = 0 Then
MsgBox ("invalid number")
' valid entry continue processing
MsgBox ("valid number")
End SubPoppa Smurf