Solved

prevent all overlapping times

Posted on 2014-10-11
16
68 Views
Last Modified: 2014-11-01
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

0
Comment
Question by:al4629740
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 5
  • 2
16 Comments
 

Author Comment

by:al4629740
ID: 40374829
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
 
LVL 46

Expert Comment

by:aikimark
ID: 40375321
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
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40375330
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:al4629740
ID: 40376055
they are Time(7) datatype.

do i still use pound signs?
0
 
LVL 46

Expert Comment

by:aikimark
ID: 40376117
What is your back-end database? (Access or SQL Server or Oracle or something else)
0
 

Author Comment

by:al4629740
ID: 40376126
Sql 2012
0
 
LVL 46

Expert Comment

by:aikimark
ID: 40376145
may be a little late to ask, but is this a VB6 or VB.Net application?`
0
 

Author Comment

by:al4629740
ID: 40376158
Vb6
0
 
LVL 46

Expert Comment

by:aikimark
ID: 40376200
I agree with Irogsinta, I think the # delimiters are probably in order.
0
 

Author Comment

by:al4629740
ID: 40376290
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
 
LVL 46

Expert Comment

by:aikimark
ID: 40376300
It's pretty much the same as a time column/data type.

What values are in your datepicker controls?
0
 

Author Comment

by:al4629740
ID: 40376309
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
 
LVL 29

Accepted Solution

by:
IrogSinta earned 500 total points
ID: 40376317
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
 

Author Comment

by:al4629740
ID: 40378698
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
 

Author Comment

by:al4629740
ID: 40378700
Why would I need to format it if it is already in DTPicker Time format on the VB6 form?
0
 

Author Closing Comment

by:al4629740
ID: 40417645
answer not fully resolved
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month8 days, 7 hours left to enroll

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question