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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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
IrogSintaCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.