none
Date field Validation RRS feed

  • Question

  • Hello,

    How does one implement data validation against two date fields(short time format) that only allows users to enter in time data in half our increments. So for example its either 1.00 or 1:30pm, but never 1:15 or 1:20..etc.  Thanks in advance.


    olu Solaru

    Thursday, July 18, 2019 3:42 PM

All replies

  • You could use a validation rule like this:

    [fieldname]=Round(48*[fieldname],0)/48

    Alternatively, you could round the value entered by the user to the nearest half hour in the After Update event of the text box bound to the field on the from used to enter/edit data:

    Private Sub fieldname_AfterUpdate()
        Me.fieldname = Round(48 * [fieldname], 0) / 48
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, July 18, 2019 4:20 PM
  • I get the following error.  There is a type mismatch in the forms's validation rule  property

    I am entering in 01:00 as my entry when this happens....


    olu Solaru

    Thursday, July 18, 2019 5:37 PM
  • Create a table with a single column of date/time data type.  Designate this column as the primary key.  In the column enter all valid times at 30 minute intervals throughout the day.  Then create and enforce a relationship on the time columns between this table and your current table.

    In your data entry form bind a combo box to the time column in the current table, using the new table as the basis for an SQL statement as the combo box's RowSource property.

    Ken Sheridan, Stafford, England

    Thursday, July 18, 2019 5:58 PM
  • well both HV and KS are super gurus so you're getting stellar advice - - - mine is more basic in that I read your statement "only allows users to enter in time data in half our increments."

    for this you could consider using a combo or list box in the form....as KS suggests, create a table of all choices and then force them to select rather than free form entry. 

    This is in fact a very common issue in terms of garbage-in whereby one set's up selectors (comboboxes) rather than just text fields cause humans do just make tons of errors.....

    Thursday, July 18, 2019 8:18 PM
  • So for example its either 1.00 or 1:30pm, but never 1:15 or 1:20..etc.  Thanks in advance.

    Hi osolaru,

    I would not use the AfterUpdate event: the program saves a different value than the users inputs. Does he see this? It is not educational.

    Instead I would use the BeforeUpdate event. In case of a wrong value (you can use the check above) the cancel argument is set to true, if necessary a message is displayed, and the user has to correct the value to an approved value in order to leave the control.

    Imb.

    Thursday, July 18, 2019 9:17 PM