prevent all overlapping times

I have a form where I enter activities for different students.  Those activities cannot overlap.

I am trying to avoid overlapping time entries using the following code.  I use DTPicker2 to enter TimeFrom and DTPicker3 to enter TimeTo.  I am using vb6.

When I enter hours for a student from 9:00am to 12:00am on a particular day and then enter a student from 9:00am to 11:00am of the same day, the code below successfully blocks the second entry.

However, when I enter hours for a student from 9:00AM to 12:00AM on a particular day and then enter a student from 10:00AM to 11:00AM of the same day, the same code does not block the entry.  What is wrong and how can I avoid the overlapping time entry?

'Check for duplicate entry-------
                   esql = "select count(*) from tblOrgHours where RegID = " & var4 & " And ActivityDate = '" & DTPicker1.Value & "' And ((HourTimeFrom >= '" & DTPicker2.Value & "' And HourTimeFrom <= '" & DTPicker3 & "') Or (HourTimeTo >= '" & DTPicker2.Value & "' And HourTimeTo <= '" & DTPicker3 & "'))"

                   rec.Open (esql), conn, adOpenDynamic, adLockOptimistic
                   
                    
                    If rec.Fields(0) > 0 Then
                        Duplicate = Duplicate + var3 & " " & var2 & vbCrLf
                        rec.Close
                        GoTo Procedure1
                    End If
                         
                   If Not rec.EOF Then rec.MoveNext
                   rec.Close

Open in new window

al4629740Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
IrogSintaConnect With a Mentor Commented:
I thought you were connecting to an Access table.  Try something like this instead:
(HourTimeFrom >= '" & Format(DTPicker2.Value,"hh:mm:ss") & "' And HourTimeFrom <= '" & Format(DTPicker3.Value,"hh:mm:ss") & "')

Open in new window

0
 
al4629740Author Commented:
I think I found the problem and here is the solution I propose.  Can someone look it over and see if it actually works for all situations.  I believe it does

esql = "select count(*) from tblOrgHours where RegID = " & var4 & " And ActivityDate = '" & DTPicker1.Value & "' And " & _
                   "((HourTimeFrom >= '" & DTPicker2.Value & "' And HourTimeFrom <= '" & DTPicker3 & "') Or " & _
                   "(HourTimeTo >= '" & DTPicker2.Value & "' And HourTimeTo <= '" & DTPicker3 & "') Or " & _
                   "(HourTimeFrom < '" & DTPicker2.Value & "' And HourTimeTo > '" & DTPicker3 & "'))"

Open in new window

0
 
aikimarkCommented:
I usually use the Between operator.  To check for overlapping time periods, just check one of the two ends of the time period being between the start and end time period.

I would expect it to look something like this:
esql = "select count(*) from tblOrgHours where RegID = " & var4 & " And ActivityDate = '" & DTPicker1.Value & "' And " & _
        "(HourTimeFrom Between '" & DTPicker2.Value & "' And '" & DTPicker3.Value & "' Or " & _
        DTPicker2.Value & " Between HourTimeFrom And HourTimeTo )" 

Open in new window

0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
IrogSintaCommented:
You were on the right track the first time.  If your HourTimeFrom and HourTimeTo are of the DATE data type, you should be using the # symbol instead of the apostrophe in your SELECT statement like so:
(HourTimeFrom >= #" & DTPicker2.Value & "# And HourTimeFrom <= #" & DTPicker3 & "#)

Open in new window

The way it is now, it is comparing the time fields as text and therefore 10 is less then 9 because the first character 1 is less than 9.

Ron
0
 
al4629740Author Commented:
they are Time(7) datatype.

do i still use pound signs?
0
 
aikimarkCommented:
What is your back-end database? (Access or SQL Server or Oracle or something else)
0
 
al4629740Author Commented:
Sql 2012
0
 
aikimarkCommented:
may be a little late to ask, but is this a VB6 or VB.Net application?`
0
 
al4629740Author Commented:
Vb6
0
 
aikimarkCommented:
I agree with Irogsinta, I think the # delimiters are probably in order.
0
 
al4629740Author Commented:
I get an error when using that formula, so I would guess that it doesn't work....My datatype is Time(7)

Are you familiar with that datatype?
0
 
aikimarkCommented:
It's pretty much the same as a time column/data type.

What values are in your datepicker controls?
0
 
al4629740Author Commented:
It would look like this

select count(*) from tblOrgHours where RegID = 74 And ActivityDate = '07/01/2014' And 
                   ((HourTimeFrom >= '09:00:00.0000000' And HourTimeFrom <= '09:00:00.0000000') Or 
                   (HourTimeTo >= '10:00:00.0000000' And HourTimeTo <= '10:00:00.0000000') Or 
                   (HourTimeFrom < '09:00:00.0000000' And HourTimeTo > '10:00:00.0000000'))

Open in new window

0
 
al4629740Author Commented:
IrogSinta,

Are you talking about adjusting what I did to this:

esql = "select count(*) from tblOrgHours where RegID = " & var4 & " And ActivityDate = '" & DTPicker1.Value & "' And " & _
                   "((HourTimeFrom >= '" & Format(DTPicker2.Value, "hh:mm:ss") & "' And HourTimeFrom <= '" & Format(DTPicker3.Value, "hh:mm:ss") & "') Or " & _
                   "(HourTimeTo >= '" & Format(DTPicker2.Value, "hh:mm:ss") & "' And HourTimeTo <= '" & Format(DTPicker3.Value, "hh:mm:ss") & "') Or " & _
                   "(HourTimeFrom < '" & Format(DTPicker2.Value, "hh:mm:ss") & "' And HourTimeTo > '" & Format(DTPicker3.Value, "hh:mm:ss") & "'))"

Open in new window

0
 
al4629740Author Commented:
Why would I need to format it if it is already in DTPicker Time format on the VB6 form?
0
 
al4629740Author Commented:
answer not fully resolved
0
All Courses

From novice to tech pro — start learning today.