Retrieve a subset from recordset for further processing 1

[BenjaminLim]

Hi,

I am trying to retrieve a subset of recordset for further processing.

Code Listing
===============

Set rstd1 = db.OpenRecordset("select Date1 from [First Date Set] order by date1", dbOpenSnapshot)

And I tried using the following to extract a subset from rstd1.

Set rstd2 = db.OpenRecordset("select Date1 from rstd1 order by Date1", dbOpenSnapshot)

=> it didn't work & produce error

And I tried :

Set rstd2 = db.OpenRecordset("select Date1 from [First Date Set] order by date1 where Date1 = rstd1!date1", dbOpenSnapshot)

=> again it failed..

Could you advice? Thanks.

Regards
Benjamin

 

[kathryn]

As far as I know, you can't do what you want in that way, but there are probably a number of ways to do what you want.

Could you give us an idea of what you are trying to do? Your two recordsets, as described above, seem to be identical.

I'm sure we can figure this out.

Kathryn

 

[BenjaminLim]

I am trying to extract from the first recordset a smaller subset based on a pre-determine date criteria.

The 2nd subset will only extract out of the full 1st recordset, a smaller set based on certain selection criteria.

Could you advice please. Thanks.

Benjamin

 

[kathryn]

Is there a reason that you have to do this in two steps?

Do you need the first recordset to get the selection criteria?

What is the selection criteria?

Sorry for all the questions.


Kathryn

 

[BenjaminLim]

Yes I need the first step as from the first recordset I will loop through all records as from the second recordset I will then further process the selected the records.

Please advice.

Thanks for look into my problem

Don't worry ask as many questions as possbile - its is from here that we all learn.

In fact I appreciate the interaction -> very healthy stuff.

Thanks a million.

Benjamin

 

[kathryn]

Benjamin,

OK, can you use logic like this:

'Get the first record set
Set rstd1 = db.OpenRecordset("select Date1 from [First Date Set] order by date1", dbOpenSnapshot)

'Loop through recordset until end
do until rstd1.eof

'Process only selected records
If rstd1!YourFieldName = YourCriteria then
Whatever you want to do
End if

rstd1.movenext
loop


I am still confused reading your original post, because the two recordsets are basically the same. rstd1 selects Date1 from a table. rstd2 selects Date1 from rstd1. Is that what you meant or is there a typo?


Kathryn