Solved

prevent all overlapping times

Posted on 2014-10-11
16
65 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
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 45

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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

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

do i still use pound signs?
0
 
LVL 45

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 45

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 45

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 45

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to create a duplicate finder Application 9 122
VBS file using code from 2nd file (txt or vbs) 4 44
Excel - Save a copy of work book 13 89
Hide vba in gp 7 106
There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

830 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