filter results

[Jamiee]

Hello,

i have a query and i must compact the results... The results are:

date, time, eventID
25-10-2004 | 11:01:00 | 0
25-10-2004 | 11:03:00 | 0
25-10-2004 | 11:06:00 | 9
25-10-2004 | 11:07:00 | 9
25-10-2004 | 11:08:00 | 9
25-10-2004 | 11:09:00 | 0
25-10-2004 | 11:10:00 | 0
25-10-2004 | 11:17:00 | 9
25-10-2004 | 11:18:00 | 9
25-10-2004 | 11:21:00 | 0
25-10-2004 | 11:22:00 | 0

I only need the first result of the '9' like this:

25-10-2004 | 11:01:00 | 0
25-10-2004 | 11:03:00 | 0
25-10-2004 | 11:06:00 | 9
25-10-2004 | 11:09:00 | 0
25-10-2004 | 11:10:00 | 0
25-10-2004 | 11:17:00 | 9
25-10-2004 | 11:21:00 | 0
25-10-2004 | 11:22:00 | 0

is there a way to do this? tried several things but now i'm stuck... please help!

Jamie

 

[llkhoutx]

The criteria for what you want is not patently obvious. It look like you're selecting ther first 9 when there's a succession of them.

That's easily done cycling through the DAO recordset of you data.

dim db as dao.database
dim rs as dao.recordeset
dim rso as dao.recordset
dim iLast as byte
set db=currentdb
set rs =db.openrecordset("QueryName",dbopensnapshot)
db.execute * from tbTemp
'note thqat tbTemp and QueryName have the same fields
iLast = 0
rs.movefirst
do until rs.eof
' all zero's added
if rs!fields(2) = 0 THEN goto Add_Record
if rs!fields(2)=9 and iLast =0 then
'only first 9 in a sequence is added
Add_Record:
rso.addnew
rso!fields(0) = rs!fields(0)
rso!fields(1) = rs!fields(1)
rso!fields(2) = rs!fields(2)
rso.update
iLast=rso!fields(2)
end if
rs.movenext
loop

rs.close
rso.close
set rs=nothing
set rso=nothing

'tbTemp now has the results (I think) you want

 

[Jamiee]

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rso As DAO.Recordset
Dim iLast As Byte
Set db = CurrentDb
Set rs = db.OpenRecordset("Query2", dbOpenSnapshot)
db.Execute * from tbTemp

it fails on the last line (Sytax Error). i'm not quite sure why, my vba knowledge is nog that hardcore but so far i'm very happy with a solution. I'll try to understand what happens.

 

[llkhoutx]

db.Execute * from tbTemp

is not valid SQL, it should be

db.Execute DELETE * from tbTemp

to delete all records in tbTemp.

I apologize for the error in my earlier posting. Of course, I don't warrant code I post to do anything.

Teach a man to fish . . .

 

[Jamiee]

probably a stupid question: but where to put the code then?! i now put it as code under a button...

now i get an Error 91 -- Object variable or With block variable not set in:
rso.AddNew

 

[llkhoutx]

I left out another line of code. After

set rs =db.openrecordset("QueryName",dbopensnapshot)

insert

set rso= db.openrecordset("tbTemp",dbopendynaset)


Note that "dbopensnapshot" means a copy and "dbopendynaset" means the actual table. It takes more time to open a table than a copy.

Note also that tbTemp has to have been defined in advance, if you haven't already created it.

Place the code where ever it means something, on some event. Clicking a button is an event.

No warranty!

 

[Jamiee]

llkhoutx THANKS!!! You saved 2004 for me

it works great!!!