1. Senior Member
Join Date
Dec 1969
Posts
572

Hi All<BR><BR>I am working on a task calendar. I wanted to give users the ability to add a task and have it be scheduled automatically by the system. I have a database with the start time and end time values for each task. If there is more than one entry in the database for that user in a 7 day time period I wanted the system to try to schedule the time most appropriate<BR><BR>I started off with <BR>&#060;--Code--&#062;<BR>Set FindAvailTime = conn.execute("Select * From dbo_schedule where AssignedUser=&#039;" & User & "&#039; AND StartTime between &#039;" & Now() - 7 & "&#039; AND &#039;" & Now() + 1 & "&#039;")<BR><BR>&#060;--End--&#062;<BR>I got all of the values out just fine but comparing the first enddate to the next record&#039;s startdate was next. I try to use an array.<BR><BR>&#060;--Code--&#062;<BR>Do Until FindAvailTime.EOF<BR>ST = FindAvailTime("StartTime") & "," & ST<BR>ET = FindAvailTime("EndTime") & "," & ET<BR>FindAvailTime.MoveNext<BR>Loop <BR><BR>ST = Left(ST, Len(ST) - 1)<BR>ST = ST & ",Empty" &#060;--------- I put this here because there are<BR> more end dates than start dates<BR>ET = Left(ET, Len(ET) - 1)<BR>A = Split(ST,",")<BR>B = Split(ET,",") <BR><BR><BR>Count = 1<BR>For Each iB In B<BR> <BR> For Each iA In A<BR> If iA &#060;&#062; "" Then <BR> C = DateDiff("n",iA,iB)<BR> D = CInt(SubCatGet("TimeEstimated"))<BR> If CBOOL(D &#060; C) = True Then<BR> SchedTaskStart = iA<BR> ETime = TimeSerial(Hour(iA), Minute(iA) + CInt(SubCatGet("TimeEstimated")), Second(iA))<BR> EDate = DateValue(iA)<BR> SchedTaskEnd = EDate & " " & ETime <BR> Exit For<BR> Exit For<BR> End If<BR> Else<BR> E = CDate(DateValue(iB) & " " & TimeSerial(17,30,00))<BR> C = DateDiff("n",E,iB)<BR> D = CInt(SubCatGet("TimeEstimated"))<BR> If CBOOL(D &#060; C) = True Then<BR> SchedTaskStart = E<BR> ETime = TimeSerial(Hour(E), Minute(E) + CInt(SubCatGet("TimeEstimated")), Second(E))<BR> EDate = DateValue(E)<BR> SchedTaskEnd = EDate & " " & ETime <BR> Exit For<BR> Exit For<BR> End If<BR><BR> End If<BR> Next<BR>Count = Count + 1 <BR>Next<BR>&#060;---End--&#062;<BR><BR>I am at a loss now. Anyone with suggestions?

2. Senior Member
Join Date
Dec 1969
Posts
572

## Does no one have any suggestions

Does no one have any suggestions

3. Mike Shaffer Guest

Hi Alex,<BR><BR>Well, a few things...<BR><BR>First, I assume that SubCatGet is either a function or a recordset value that returns the estimated time value (in minutes) for the task at hand, correct? If so, it should be moved in front of the DO...LOOP (no need to recalculate/re-reference it everytime you go through the loop if it doesn&#039;t change, yes?)<BR><BR>*PERSONAL PREFERENCE* I don&#039;t really like the cbool(d&lt;c) bit... nobody really does this and it forces an unnecessary type-conversion... I would recommend simply:<BR><BR>if D &lt; C then<BR><BR>Third, you didn&#039;t really state what the problem is... are you asking if there&#039;s a better way? or are you receiving an error? or ??<BR><BR>Here&#039;s my guess... you probably are asking because the routine does not seem to work, but it&#039;s not returning any errors. Is that correct? If so, the biggest possible cause for that problem is the consecutive &#039;EXIT FOR&#039; statements which you are using. These will not work as you want them to. When the first EXIT FOR is encountered, the code will skip straight to the code following the associated NEXT statement, thereby skipping the second EXIT FOR. You can try this code to verify/illustrate what I mean:<BR><BR><BR>====================<BR>for x = 1 to 100<BR>&nbsp;&nbsp;&nbsp; for y = 1 to 10<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; response.write x & " - " & y & "&lt;br&gt;"<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs p; if y = 2 then<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp; exit for<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;& nbsp;&nbsp; exit for<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; end if<BR>&nbsp;&nbsp;&nbsp; next<BR>next<BR>====================<BR><BR>So... if you restructure your logic a bit to handle this problem, your code should work (the only other curious thing I noted was that when you find an &#039;empty&#039; time slot, you seem to schedule the task starting from the &#039;end time&#039; of that empty time slot... is that what you intended?)<BR><BR>Anyway, I hope this helps somewhat. ;-)<BR>

4. Mike Shaffer Guest

Alex,<BR><BR>Did this solve your problem? Did you get it working?

5. Alex Erwin Guest