Building one SQL query on VBA!:)

[mfaqueiroz]

Hi Everyone!
I have an input tab with many registers with different satatus from machines (on,off,alarm,error...).
I need one virtual query(Tab_Alarm), builded from my input table "Tab", where I want to see all the events 4 seconds before with the status "Alarm". However in case that it finds the status "On" the query should stop.
For exemple:

10:10:16 Alarm
10:10:17 Alarm
10:10:17 Alarm
10:10:18 On
10:10:19 Alarm
10:10:19 Off
10:10:19 Error
10:10:20 My analysed register

The event ot 10:10:17 is 4 seconds before my register, however i have one event with the satus "ON" after that, so I should stop the query on 10:10:19.

There is my code:
MyCurrentDate= !Me.Date.Fields().Value
Date4= DateAdd("s",-4,MyCurrentDate)

Set TAB_Alarm = dbs.OpenRecordset("SELECT Tab.ID, Tab.[My date], Tab.[ms], Tab.Status WHERE (((Tab.[Rtu date])<=#" &Date4 & "#) AND (((Tab.[Rtu date])>=#" & MyCurrentDate & "#))) And TabInst.[Status] = 'ALARM', DB_OPEN_DYNASET)

That gives me all the Alarms 4 seconds before and don't stop when finds one "ON"

Could you please help me adding the condiction:
if finds one "ON" then stop?


I really appreciate your help, thank you!

 

[CJ_London]

I think I understand what you are saying. in your sample data, you only want the first 4 records to be reported
10:10:16 Alarm
10:10:17 Alarm
10:10:17 Alarm
10:10:18 On


if this is correct then since you are using VBA you can create another recordset to detect the ON record, if it exists then modify your date4 value to the date of the on record.