Solved

prevent all overlapping times

Posted on 2014-10-11
16
61 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
  • 9
  • 5
  • 2
16 Comments
 

Author Comment

by:al4629740
Comment Utility
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 45

Expert Comment

by:aikimark
Comment Utility
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
Comment Utility
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
 

Author Comment

by:al4629740
Comment Utility
they are Time(7) datatype.

do i still use pound signs?
0
 
LVL 45

Expert Comment

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

Author Comment

by:al4629740
Comment Utility
Sql 2012
0
 
LVL 45

Expert Comment

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

Author Comment

by:al4629740
Comment Utility
Vb6
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 45

Expert Comment

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

Author Comment

by:al4629740
Comment Utility
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 45

Expert Comment

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

What values are in your datepicker controls?
0
 

Author Comment

by:al4629740
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
answer not fully resolved
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
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.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now