Using rst.Find criteria 1

[PaulBricker]

I am trying to use the rst.Find method with multiple criteria. The statement I have that doesn't work is

[blue]rst.Find &quot;[RunDate] <= '&quot; & Date() & &quot;' And [RunStatus] = False &quot;[/blue]

Now
[blue]rst.Find &quot;[RunDate] <= '&quot; & Date() & &quot;'&quot;[/blue]
works by itself and
[blue]rst.Find &quot;[RunStatus] = False&quot;[/blue]
works by itself, but the two together do not. Can anyone point out a syntax change that will take care of this.
Thanks

Paul

 

[PaulBricker]

We can forget this one. In doing a little more searching in the Access VBA forum,I have found that the Find method does not work with multiple criteria so I ended up using a select statement in my rst.Open command and using the recordset to update my table.
Thanks anyway.

Paul

 

[cmmrfrds]

You can use multiple criteria just not with the Find method. Replace with the Filter method.

rst.Filter &quot;[RunDate] <= '&quot; & Date() & &quot;' And [RunStatus] = False &quot;

 

[PaulBricker]

Would there be some advantage to using the filter method? We have a Work Order system that produces Preventive Maintenance work orders and Scheduled work orders. The PM and Scheduled work orders are entered into a form with specific run dates. When our main WO system is opened, it looks at the PM and Scheduled tables to see if any WO's need to be produced that day. If there are, it writes new WO's in our WO system, resets the RunDate and RunStatus fields in the PM and Scheduled tables and then prints out the new WO's. We've run it for 10 years using DAO but I'm in the process of migrating this system to ADO in anticipation of moving it from Access to SQLServer. Hopefully it will be a while before I have to deliver the whole thing, but I'm just starting the process so I'm still somewhat ignorant of which ADO methods would be better.
Thanks for the reply.

Paul

 

[cmmrfrds]

I was strictly looking at the ADO Filter method as a replacement for the DAO Find method. I very seldom use recordset filtering, but instead requery with a different SQL select criteria. The Filter method works very well if you want to pick out subset of a recordset and is accumulative so that you could make finer and finer subsets.
The Filter can be turned off by
Set rst.Filter = adFilterNone
and then it is back to the unfiltered recordset.

The Filter could be used for something like a temporary mod to the recordset for a certain purpose. Lets say there was not sufficient information to categorize the recordset in the query, but by looping through the recordset and changing a field the category could be set in each record and then filtered or sorted and then displayed or whatever the need. First disconnect the recordset so that it does not update the source, then loop through and add a sequence number and category and then sort by the category or filter as needed and then display and then destroy the recordset.

Another use is in place of a complex or multi dimensional array. You could make a temporary recordset and then use the Sort and Filter. Here is an example.

Dim rs As ADODB.Recordset
Dim varArray As Variant
Set rs = New ADODB.Recordset

With rs.Fields
.Append &quot;myid&quot;, adInteger
.Append &quot;mydesc&quot;, adVarChar, 50, adFldIsNullable
End With

varArray = Array(&quot;myid&quot;, &quot;mydesc&quot

With rs
.Open
.AddNew varArray, Array(1, &quot;first desc&quot
.AddNew varArray, Array(2, &quot;second desc&quot
.AddNew varArray, Array(3, &quot;third desc&quot
.AddNew varArray, Array(4, &quot;fourth desc&quot
End With
rs.MoveFirst
While Not rs.EOF
Debug.Print rs!myid; &quot; &quot;; rs!mydesc
rs.MoveNext
Wend

rs.Sort = &quot;myid DESC&quot;

rs.MoveFirst
While Not rs.EOF
Debug.Print rs!myid; &quot; &quot;; rs!mydesc
rs.MoveNext
Wend

rs.Filter = &quot;myid = 3&quot;

rs.MoveFirst
While Not rs.EOF
Debug.Print rs!myid; &quot; &quot;; rs!mydesc
rs.MoveNext
Wend

rs.Filter = adFilterNone

rs.Close
Set rs = Nothing

 

[PaulBricker]

Thanks, I will look at it more closely today. I appreciate you taking the time to flesh that out for me some.

Paul