Results 1 to 6 of 6
  1. #1
    tykra is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2017
    Posts
    4

    Validation of Data Entry

    I need to validate entered data to make sure time ranges are not overlapping with ranges already entered.



    Users will use a form to enter events that take place on the same date. The main fields on the form are tDate, tStartTime, and tEndTime which for example will show something like 8:00:00 AM for start time and 12:00:00 PM for end time. There is also a field for empID to store entered times for each employee on a particular date.

    I have a submit button set as boolean and use before update to check the status of that button so no data is entered into the table until the submit button is clicked. When clicked I have some standard validation to make sure fields on the form are not null and check to make sure the tEndTime > tStartTime and if so msg user they must select time an end time after start time. What I need to come up with is validation rules that will check to see if a newly entered time range overlaps any of those already entered by that empID and on that tDate and msg the user to correct this issue before the update will take place.

    There may be multiple entries by an employee on any given date.

    Typical data might be as follows.

    tDate tStartTime tEndTime
    5/1/2017 8:00 12:00
    5/1/2017 12:30 2:00
    5/1/2017 2:00 5:00

    When entering a new record users should not able to select 8:30 to 11:00, they should not be able to select 12:00 to 1:00 - I dont want users to create any overlapping conflict.

    I am struggling with how to create validation rule so any guidance will be greatly appreciated - thanks!

  2. #2
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If I understand, they should not be able to create an event that starts OR ends anywhere between 8:00 AM and 5:00 PM on 05/01/2017, although you aren't revealing AM or PM. If that's the case, I think if you use the Min of Start and the Max of end, and if starttime is between those values OR if endtime is between those values on a given date, then the validation fails. This presumes that the start and end times will not span over different dates.

    You've provided a couple of examples of what cannot be, but none for what can be.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    tykra is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2017
    Posts
    4
    I posted for help on this and later figured out what I needed to do.

    Code:
    If Dcount ("*", "Q_subformTimes", "[tStartTime] < '" &  Me.txtEndTime &"'  AND [tEndTime] > '" &  Me.txtStartTime  &"'  AND [empID] = ' " & varID &" ' AND  [tDate] = "& Format(Me.txtDate, "\#yyy\-mm\-dd\#)) > 0 Then
    MsgBox "Error overlapping time entered"
    Basically this looks into the table via query to to see if a new time falls within the range of an already entered start/end time range where the date and user match a particular record and if so it returns and error.

    I get so frustrated some times I seek help and before ambo arrives I am up and running again.

    Thanks for the reply though - have a great day!

  4. #4
    tykra is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2017
    Posts
    4
    Actually this does not appear to be working like I had hoped.

    The items are shown in a filtered (by tdate and empID) subform with an edit/delete link. When clicking edit you are not able to save it back because it sees it as a duplicate.

    Is there anyway to ignore the current row in a dlookup?

  5. #5
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Is there anyway to ignore the current row in a dlookup?
    By using criteria in the lookup that ignores a value of a field in the current record.
    I have a submit button set as boolean and use before update
    I wouldn't have thought it possible to call a BeforeUpdate event from a button. It is certainly an odd approach if you are doing it on the same form in use.

    I don't see why you'd use DCount. For your situation, what good is the value for the number of records in a domain? Based on your sample data, the result of that calculation is 3. Plus, according to your expression and examples, if I try to create an event that starts at 09:00 and ends at 14:00 (9 AM to 2:00 PM) it will accept it because for the first record (5/1/2017 8:00 12:00) the comparison (I used military time to show where I think the user entered values are) is

    8 < 13.00 AND 12.00 > 14.00

    which is False. Still have no idea if this is based on a record by record comparison, or from the start of the first to the end of the last record. If the former, then what happens when a user creates more than one event?

  6. #6
    tykra is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2017
    Posts
    4
    Yes, thats what I did just added another AND in my code to say ID <> me!ID then it ignores the current record when editing it.

    The button is declared as a boolean and only becomes = true when the button is clicked. The check for the status of the button is done before update.

    dcount counts any rows that match my criteria if its greater than 0 there is an overlap.

    5/1/2017 8:00 12:00
    5/1/2017 12:30 2:00
    5/1/2017 2:00 5:00

    If a new event is entered as 9 - 2 it is not accepted as it overlaps existing times.

    Existing start time < new end time
    Existing end time > new start time

    Any of the existing start times less than the new end time? Yes
    Any of the existing end times greater than the new start time? Yes

    If 8:00 - 12:00 exist and you try to enter 9:00-2:00 it will fail the test.

    Existing start time 8 is less than new end time 14
    Existing end time 12 is grater than new start time 9

    Quote Originally Posted by Micron View Post
    By using criteria in the lookup that ignores a value of a field in the current record.
    I wouldn't have thought it possible to call a BeforeUpdate event from a button. It is certainly an odd approach if you are doing it on the same form in use.

    I don't see why you'd use DCount. For your situation, what good is the value for the number of records in a domain? Based on your sample data, the result of that calculation is 3. Plus, according to your expression and examples, if I try to create an event that starts at 09:00 and ends at 14:00 (9 AM to 2:00 PM) it will accept it because for the first record (5/1/2017 8:00 12:00) the comparison (I used military time to show where I think the user entered values are) is

    8 < 13.00 AND 12.00 > 14.00

    which is False. Still have no idea if this is based on a record by record comparison, or from the start of the first to the end of the last record. If the former, then what happens when a user creates more than one event?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. A password validation field upon entry or click
    By txczshooter in forum Forms
    Replies: 14
    Last Post: 08-11-2015, 10:52 AM
  2. Replies: 27
    Last Post: 06-06-2013, 04:31 AM
  3. Data entry validation rule in Form
    By accessn00bie in forum Access
    Replies: 3
    Last Post: 12-07-2012, 01:11 PM
  4. Using Validation rules to restrict certain types of data entry.
    By Long Tom Coffin in forum Database Design
    Replies: 3
    Last Post: 07-23-2012, 10:38 AM
  5. Datasheet entry validation
    By Fstrategic in forum Access
    Replies: 2
    Last Post: 01-19-2012, 07:07 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums