Window a Time Frame

[jutlaux]

What I am trying to do is take a time field from 1 query and put a window
around it and from another query look up an alarm event within that time
frame based on a specific event on a specific day on a specific machine in 1
of the 2 underlying queries. I have gotten this to work when on the
specified machine and day the event in question only happens once. The
problem I have is that if the event happens more then once on the same day
the query returns nothing. I apologize for the long post, but wanted to
explain all that that I have to better assist with answers.

My question is two fold 1) Is this the best way to do this? 2) If o then
what can I do to get the data when the event happens more then once on the
same day.

Thanks!

I have 1 query (qryAlarmsMaster) that is used to join 2 queries
(qryAlarmShutdown & qryPRESS_ALARM_HISTORY).

qryAlarmShutdown is the query that has the event that I am looking for, the
specific date of the event, and time that I am trying to window around.
Within this query I have created a =/- 10 minute window with the following
statement

Time_Window1:
DateAdd("n",10,TimeSerial(Hour([CURE_START_DATE_TIME]),Minute([CURE_START_DATE_TIME]),Second([CURE_START_DATE_TIME])))
Time_Window2:
DateAdd("n",-10,TimeSerial(Hour([CURE_START_DATE_TIME]),Minute([CURE_START_DATE_TIME]),Second([CURE_START_DATE_TIME])))

both these windows are giving me the right info.

qryPRESS_ALARM_HISTORY has a field [Alarm_Start_Time2], which is the time
fram I am windowing around

In qryAlarmsMaster I have 2 fields that are joined together

qryAlarmShutdown.Cure_Start_Date is joined to
qryPRESS_ALARM_HISTORY.Alarm_Start_Date and
qryAlarmShutdown.PRESS_NAME is joined to qryPRESS_ALARM_HISTORY.PRESS_NAME

In qryAlarmsMaster I have 5 fields: [Cure_Start_Date], [PRESS_NAME],
[SHUTDOWN_ALARM_COND], [Alarm_Start_Time2], & [ALARM_NUMBER]
[Cure_Start_Date] -> specified day of the event
[PRESS_NAME] -> specific machine
[SHUTDOWN_ALARM_COND] -> specific events
[Alarm_Start_Time2] -> Window time frame
[ALARM_NUMBER] -> looked up alarm event

What I do to run this query is in the criteria section I type in a date for
[Cure_Start_Date], a value for [PRESS_NAME], [SHUTDOWN_ALARM_COND] , and for
[Alarm_Start_Time2] I have Between [Time_Window1] And [Time_Window2]

I have passed the SQL to maybe help out.

SELECT qryAlarmShutdown.Cure_Start_Date, qryAlarmShutdown.PRESS_NAME,
qryAlarmShutdown.SHUTDOWN_ALARM_COND,
qryPRESS_ALARM_HISTORY.Alarm_Start_Time2, qryPRESS_ALARM_HISTORY.ALARM_NUMBER
FROM qryAlarmShutdown LEFT JOIN qryPRESS_ALARM_HISTORY ON
(qryAlarmShutdown.PRESS_NAME = qryPRESS_ALARM_HISTORY.PRESS_NAME) AND
(qryAlarmShutdown.Cure_Start_Date = qryPRESS_ALARM_HISTORY.Alarm_Start_Date)
WHERE (((qryAlarmShutdown.Cure_Start_Date)=#1/14/2009#) AND
((qryAlarmShutdown.PRESS_NAME)="V14") AND
((qryAlarmShutdown.SHUTDOWN_ALARM_COND)=3 Or
(qryAlarmShutdown.SHUTDOWN_ALARM_COND)=11 Or
(qryAlarmShutdown.SHUTDOWN_ALARM_COND)=63 Or
(qryAlarmShutdown.SHUTDOWN_ALARM_COND)=64) AND
((qryPRESS_ALARM_HISTORY.Alarm_Start_Time2) Between [Time_Window1] And
[Time_Window2]));

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Q: Does the Alarm_Start_Date and the Cure_Start_Date have both the date
and time stored in them? If so, then your join may need to pull just
the DateValue() from them:

.... AND (DateValue(qryAlarmShutdown.Cure_Start_Date) =
DateValue(qryPRESS_ALARM_HISTORY.Alarm_Start_Date) ...

Otherwise, you'll be comparing dates and Times and the left side might
not equal the right side, but will be in the 10 minute window.

Cure_Start_Date (1/15/09 09:30:25 PM)
does not equal
Alarm_Start_Date (1/15/09 09:25:25 PM)

Here are 2 suggestions that probably won't solve your problem, but will
make maintenance easier:

1. You don't have to use the TimeSerial() function if the
CURE_START_DATE_TIME is a true Date/Time value. Instead use the
TimeValue() function, or NO FUNCTION at all - the DateAdd() function
will still work correctly on a Date/Time value of "1/15/2009 09:50:30
PM":

DateAdd("n", -10, "1/15/2009 09:50:30 PM") = 1/15/2009 09:40:30 PM

If you just want the time value to show for the result of the DateAdd()
function then wrap the DateAdd() function in the TimeValue() function:

TimeValue(DateAdd("n", -10, "1/15/2009 09:50:30 PM")) = 09:40:30 PM


2. The criteria of the SQL statement can be better stated like this
(those pesky parentheses Access puts around everything can seriously
screw up criteria that has AND and OR expressions). Note the IN ()
operator on SHUTDOWN_ALARM_COND - it works the same as all those ORs:

WHERE qryAlarmShutdown.Cure_Start_Date=#1/14/2009#)
AND qryAlarmShutdown.PRESS_NAME="V14"
AND qryAlarmShutdown.SHUTDOWN_ALARM_COND In (3,11,63,64)
AND qryPRESS_ALARM_HISTORY.Alarm_Start_Time2 Between [Time_Window1] And
[Time_Window2]

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSXo04YechKqOuFEgEQI1egCfYrbGbPETZIwn/wKYwSz4sOOYJDAAn2bu
Ite2jvI04rv5W+ggoepCPfZw
=jXTo
-----END PGP SIGNATURE-----

What I am trying to do is take a time field from 1 query and put a window
around it and from another query look up an alarm event within that time
frame based on a specific event on a specific day on a specific machine in 1
of the 2 underlying queries. I have gotten this to work when on the
specified machine and day the event in question only happens once. The
problem I have is that if the event happens more then once on the same day
the query returns nothing. I apologize for the long post, but wanted to
explain all that that I have to better assist with answers.

My question is two fold 1) Is this the best way to do this? 2) If o then
what can I do to get the data when the event happens more then once on the
same day.

Thanks!

I have 1 query (qryAlarmsMaster) that is used to join 2 queries
(qryAlarmShutdown & qryPRESS_ALARM_HISTORY).

qryAlarmShutdown is the query that has the event that I am looking for, the
specific date of the event, and time that I am trying to window around.
Within this query I have created a =/- 10 minute window with the following
statement

Time_Window1:
DateAdd("n",10,TimeSerial(Hour([CURE_START_DATE_TIME]),Minute([CURE_START_DATE_TIME]),Second([CURE_START_DATE_TIME])))
Time_Window2:
DateAdd("n",-10,TimeSerial(Hour([CURE_START_DATE_TIME]),Minute([CURE_START_DATE_TIME]),Second([CURE_START_DATE_TIME])))

both these windows are giving me the right info.

qryPRESS_ALARM_HISTORY has a field [Alarm_Start_Time2], which is the time
fram I am windowing around

In qryAlarmsMaster I have 2 fields that are joined together

qryAlarmShutdown.Cure_Start_Date is joined to
qryPRESS_ALARM_HISTORY.Alarm_Start_Date and
qryAlarmShutdown.PRESS_NAME is joined to qryPRESS_ALARM_HISTORY.PRESS_NAME

In qryAlarmsMaster I have 5 fields: [Cure_Start_Date], [PRESS_NAME],
[SHUTDOWN_ALARM_COND], [Alarm_Start_Time2], & [ALARM_NUMBER]
[Cure_Start_Date] -> specified day of the event
[PRESS_NAME] -> specific machine
[SHUTDOWN_ALARM_COND] -> specific events
[Alarm_Start_Time2] -> Window time frame
[ALARM_NUMBER] -> looked up alarm event

What I do to run this query is in the criteria section I type in a date for
[Cure_Start_Date], a value for [PRESS_NAME], [SHUTDOWN_ALARM_COND] , and for
[Alarm_Start_Time2] I have Between [Time_Window1] And [Time_Window2]

I have passed the SQL to maybe help out.

SELECT qryAlarmShutdown.Cure_Start_Date, qryAlarmShutdown.PRESS_NAME,
qryAlarmShutdown.SHUTDOWN_ALARM_COND,
qryPRESS_ALARM_HISTORY.Alarm_Start_Time2, qryPRESS_ALARM_HISTORY.ALARM_NUMBER
FROM qryAlarmShutdown LEFT JOIN qryPRESS_ALARM_HISTORY ON
(qryAlarmShutdown.PRESS_NAME = qryPRESS_ALARM_HISTORY.PRESS_NAME) AND
(qryAlarmShutdown.Cure_Start_Date = qryPRESS_ALARM_HISTORY.Alarm_Start_Date)
WHERE (((qryAlarmShutdown.Cure_Start_Date)=#1/14/2009#) AND
((qryAlarmShutdown.PRESS_NAME)="V14") AND
((qryAlarmShutdown.SHUTDOWN_ALARM_COND)=3 Or
(qryAlarmShutdown.SHUTDOWN_ALARM_COND)=11 Or
(qryAlarmShutdown.SHUTDOWN_ALARM_COND)=63 Or
(qryAlarmShutdown.SHUTDOWN_ALARM_COND)=64) AND
((qryPRESS_ALARM_HISTORY.Alarm_Start_Time2) Between [Time_Window1] And
[Time_Window2]));