Need a Way to LOOP This 1

[bistro7]

I am running this CASE from 6:00 a.m. to 5:00 p.m. updating a query every 15 minutes. The interval also updates by 1. e.g. 6:00-6:15 =1, 6:15-6:30 = 2, etc... I am looking for a way to put this in a loop for better coding. Any help would be appreciated.

Select Case strInterval
Case "06:00:00 AM"
txtOmega2 = "1"
DoCmd.OpenQuery "qryNewSch"

Case "06:15:00 AM"
txtOmega2 = "2"
DoCmd.Close acQuery, "qryNewSch", acSaveNo
DoCmd.OpenQuery "qryNewSch"

Case "06:30:00 AM"
txtOmega2 = "3"
DoCmd.Close acQuery, "qryNewSch", acSaveNo
DoCmd.OpenQuery "qryNewSch"

Case "06:45:00 AM"
txtOmega2 = "4"
DoCmd.Close acQuery, "qryNewSch", acSaveNo
DoCmd.OpenQuery "qryNewSch"

 

[PWise]

Create a table
[tt]
txtOmega2,strInterval,Starttime, endtime
1, 06:00:00 AM ,06:00:00 AM ,06:14:59 AM
2, 06:15:00 AM ,06:15:00 AM ,06:29:59 AM
.....


[/tt]

i dont know what is in your qryNewSch query

but join this table to the query

 

[bistro7]

Thanks..qryNewSch displays the schedule for the team each interval. The query runs each time the interval changes and displays the updated schedule for that specific interval. The schedule is diplayed on a large monitor. The trigger to run the query is the interval change (timer control). What I am trying to do in move away from using CASE and use a loop:

Name Time State Interval
Agent 1 6:30 - 6:45 Phone 3
Agent 2 6:30 - 6:45 Phone 3
Agent 3 6:30 - 6:45 Break 3
Agent 4 6:30 - 6:45 Phone 3
Agent 5 6:30 - 6:45 Break 3
Agent 6 6:30 - 6:45 Meeting 3

 

[traingamer]

You can calculate the interval very simply:

txtOmega = int((DateTimeValue - int(DateTimeValue)) * 86400 / 900) - 24

This takes the actual datetime value of your interval (not the string value) - datetimes are double precison numbers where the integer portion is the date and the decimal portion is the time. e.g. 27468.96875
where 27468 is March 15, 1975
and .96875 is 11:15:00 P.M.

The algorithm above eliminates the date portion leaving just the time

((DateTimeValue - int(DateTimeValue))

then it multiplies the remainder by 86400 (number of seconds in the day) and divides by 900 (the number of seconds in fifteen minutes) to get the 15 minute section of the day and reduces that to an integer (the leading int)

* 86400 / 900

subtract the segments that occur before 6:00 am so that 6:00 am is segment 1

-24



note: Instead of multiplying by 96400 and dividing by 900 you would get the same result if you multiply the intermediate result by 96 (number of 15 minute segments in a day).




Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[traingamer]

once you do that, you only have two cases - the first one of the day and all of the rest:

txtOmega = int((DateTimeValue - int(DateTimeValue)) * 86400 / 900) - 24

if txtOmega > 1 then
   DoCmd.Close acQuery, "qryNewSch", acSaveNo
end if

DoCmd.OpenQuery "qryNewSch"

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[bistro7]

traingamer, thanks this is intersting. I am going to have to try this. I will follow-up when I get back in tomorrow.

 

[bistro7]

trangamer, great idea...I got the code to work and it is working fine with the exception of one issue. The query is constantly running and is flashing every second. Is there a way to have this run just once every 15 minutes or just to run when the interval changes?

 

[traingamer]

Aren't you using a timer between iterations?

If not, use a timer to pause between loops. We are only seeing the code that you've provided, so each of makes assumptions...

Set it to run every 15 minutes or every 5 minutes, or every minute (if it's important to switch at or near the 15 minute marks).

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[bistro7]

Got it....that worked; thanks for you help.

 

[Emtucifor]

Greg,

Be careful with using the int() function on time values that have been multiplied. VB converts the Date data type to Double when you do math on it, and it is a non-exact data type which can yield surprising (and wrong) results. Evaluate these expressions in Access for an example. I was trying this in my code and getting the wrong answer after dividing by 96 again.

(#7/5/2010 1:00:00 PM#) * 96, int((#7/5/2010 1:00:00 PM#) * 96)

bistro7,

Here's some code to show how you might use the built-in timer in Access forms (assuming there is a form open all the time). Rather than a form timer, you can also use Windows API calls to get the same effect.

Private Const StartTime = #6:00:00 AM#
Private Const EndTime = #5:00:00 PM#

Private Sub Form_Load()
   IntervalSet
End Sub

Private Sub IntervalSet()
   Dim N As Date
   Dim D As Date
   Dim Interval As Long
   N = Now
   D = DateAdd("n", Int((DateDiff("n", Int(N), N) + 15) / 15) * 15, Int(N))
   If D < Int(N) + StartTime Then
      D = Int(N) + StartTime
   ElseIf D > Int(N) + EndTime Then
      D = Int(N) + StartTime + 1
   End If
   Interval = DateDiff("s", N, D)
   Debug.Print "setting timer interval to " & Interval * 1000 & ", N: " & N & " D: " & D
   Me.TimerInterval = Interval * 1000
End Sub

Private Sub QueryLoad()
   If CurrentData.AllQueries("qryNewSch").IsLoaded Then DoCmd.Close acQuery, "qryNewSch", acSaveNo
   DoCmd.OpenQuery "qryNewSch"
   Debug.Print Now() & " - Run Query"
End Sub

Private Sub Form_Timer()
   Me.TimerInterval = 0
   QueryLoad
   IntervalSet
End Sub