Results 1 to 4 of 4
  1. #1
    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



  2. #2
    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/ 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 14:48.

  3. #3
    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
    exit sub
    end if

    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 23:05.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    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.

    Sub test_valid_entry()
    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)
    Debug.Print field_length

    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")
    Exit Sub
    End If
    Next string_counter

    ' valid entry continue processing
    MsgBox ("valid number")

    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts