query with date limitations and expressions

[cporter]

I have a table for our workorders. The columns are time down, day
down, time up, day up, machine ID, employee, action, comments,
workorder number(key), and total hours down. I need to search the table
for workorders between dates and times entered by a users (parameter
search). Those records need to be processed by machine ID and the
elapsed time between down and up calculated, number of event counted,
and some other calculations made. Below is part of the code we use in
dBase IV. How do I implement this in Access?




SEEK mmachid


SCAN REST FOR (machid = mmachid.AND.daydown +
(VAL(SUBSTR(timedown,1,2))+VAL(SUBSTR(timedown,4,2))/60)/24 => startday
+ mstartime .AND. daydown +
(VAL(SUBSTR(timedown,1,2))+VAL(SUBSTR(timedown,4,2))/60)/24 <= stopday
+ mstoptime).OR.(dayup = CTOD(" / / ") .AND. timedown <> "
").OR.(machid = mmachid .AND. dayup +
(VAL(SUBSTR(timeup,1,2))+VAL(SUBSTR(timeup,4,2))/60)/24 => startday +
mstartime .AND. dayup +
(VAL(SUBSTR(timeup,1,2))+VAL(SUBSTR(timeup,4,2))/60)/24<= stopday +
mstoptime).OR. (machid = mmachid.AND.((thd/24) + daydown +
(VAL(SUBSTR(timedown,1,2))+VAL(SUBSTR(timedown,4,2))/60)/24) => stopday
+ mstoptime .AND. daydown +
(VAL(SUBSTR(timedown,1,2))+VAL(SUBSTR(timedown,4,2))/60)/24 <= stopday
+ mstoptime) WHILE machid = mmachid


IF dayup = CTOD(" / / ").AND.daydown +
(VAL(SUBSTR(timedown,1,2))+VAL(SUBSTR(timedown,4,2))/60)/24 <=
mstartday + mstartime
STORE mperiod TO zthd
STORE 1 + cnt TO cnt
ELSE

IF (dayup = CTOD(" / / ").AND. daydown +
(VAL(SUBSTR(timedown,1,2))+VAL(SUBSTR(timedown,4,2))/60)/24 =>
mstartday + mstartime .AND. daydown +
(VAL(SUBSTR(timedown,1,2))+VAL(SUBSTR(timedown,4,2))/60)/24 <= mstopday
+ mstoptime)
STORE (((mstopday + mstoptime) - (daydown +
(VAL(SUBSTR(timedown,1,2))+VAL(SUBSTR(timedown,4,2))/60)/24))*24) TO
zthd
STORE 1 + cnt TO cnt
ELSE

IF daydown +
(VAL(SUBSTR(timedown,1,2))+VAL(SUBSTR(timedown,4,2))/60)/24 <=
mstartday + mstartime .AND. dayup +
(VAL(SUBSTR(timeup,1,2))+VAL(SUBSTR(timeup,4,2))/60)/24 => mstopday +
mstoptime
STORE mperiod TO zthd
STORE 1 + cnt TO cnt
ELSE

IF daydown +
(VAL(SUBSTR(timedown,1,2))+VAL(SUBSTR(timedown,4,2))/60)/24 =>
mstartday + mstartime .AND. dayup +
(VAL(SUBSTR(timeup,1,2))+VAL(SUBSTR(timeup,4,2))/60)/24 => mstopday +
mstoptime
STORE (((mstopday + mstoptime) - (daydown +
(VAL(SUBSTR(timedown,1,2))+VAL(SUBSTR(timedown,4,2))/60)/24))*24) TO
zthd
STORE 1 + cnt TO cnt
ELSE

IF dayup +
(VAL(SUBSTR(timeup,1,2))+VAL(SUBSTR(timeup,4,2))/60)/24 <= mstopday +
mstoptime .AND. daydown +
(VAL(SUBSTR(timedown,1,2))+VAL(SUBSTR(timedown,4,2))/60)/24 <=
mstartday + mstartime
STORE (((dayup +
(VAL(SUBSTR(timeup,1,2))+VAL(SUBSTR(timeup,4,2))/60)/24) - (mstartday +
mstartime))*24) TO zthd
STORE 1 + cnt TO cnt
ELSE

IF daydown +
(VAL(SUBSTR(timedown,1,2))+VAL(SUBSTR(timedown,4,2))/60)/24 =>
mstartday + mstartime .AND. dayup +
(VAL(SUBSTR(timeup,1,2))+VAL(SUBSTR(timeup,4,2))/60)/24 <= mstopday +
mstoptime
STORE thd TO zthd
STORE 1 + cnt TO cnt
ENDIF
ENDIF
ENDIF
ENDIF
ENDIF
ENDIF

 

[Duane Hookom]

Create a form for entering your date/time values (frmRptDates with two text
boxes txtStartDateTime and txtEndDateTime). Assuming some field names in
your table (as well as your table name) try this SQL to display the
MachineID, count of events, and sum of minutes.

SELECT MachineID, Count(MachineID) AS NumOf,
Sum(DateDiff("n",[DayDown]+[TimeDown],[DayUp]+[TimeUp])) AS DownMinutes
FROM tblDowntime
WHERE ([DayDown]+[TimeDown])<=[Forms]![frmRptDates]![txtEndDateTime]
AND ([DayUp]+[TimeUp])>=[Forms]![frmRptDates]![txtStartDateTime]
GROUP BY MachineID;