[RESOLVED] nested filtering?

[wolf99]

Hi [vb6; DAO; MSAccess]

I need to filter a recordset then filter it again. does this work by simply

db_rs.Filter = "(field1 = string1)"
db_rs.Movefirst
string2 = field2
db_rs.Filter = "(field2 = string2)"

so basically the first set is filtered, and a field fromthe first record of the filtered set is used to create a filtered "sub-set" of the originall filter.

should this work or is there a better way to do it?
thanks


just thought maybe it should be:

db_rs.Filter = "(field1 = string1)"
db_rs.Movefirst
string2 = field2
db_rs.Filter = "((field1 = string1) AND (field2 = string2))"

??

[leinad31]

Try the one with an AND, let us know if the filter takes effect.

[Hack]

A much better way to do it is using SQL. Are you familiar with that?

[wolf99]

quick answer: Nope

am using Dao wit vb6 and access on win 98 pc's that dont have acces installed. I know working without SQL works on this setup so logical to try to carry on using it...

Thanks though....

[Ellis Dee]

SQL is built into DAO.

You can learn all you need to know about SQL in less than five minutes. Create any Select query in Access, then switch from design view to SQL view. Copy and paste that SQL text to the red part of the openrecordset command:

Dim db As Database
Dim rst As Recordset

Set db = OpenDatabase("c:\MyDatabase.mdb")
Set rst = db.OpenRecordset("SELECT * FROM MyTable WHERE ((field1 = string1) AND (field2 = string2));", dbOpenDynaset)
Do While Not rst.EOF
    rst.MoveNext
Loop
rst.Close
Set rst = Nothing
db.Close

Action queries (Insert/Update/Delete) are even easier, because you don't even need a recordset. Again set it up as a query, then just switch to SQL view and copy the SQL statement over the red part:

Dim db As Database

Set db = OpenDatabase("c:\MyDatabase.mdb")
db.Execute "DELETE * FROM MyTable WHERE ((field1 = string1) AND (field2 = string2));"
db.Close

That's pretty much all there is to it. The only other thing you need to know to get you up and running is to use dbOpenSnapshot instead of dbOpenDynaset if you don't need to edit the records.

[Hack]

There is also a link to an SQL tutorial in our FAQ section if you wanted to get more in depth.

Learning SQL is something that will enhance your marketability as well as make your programming life a whole lot simpler.

[Ellis Dee]

Note that the users don't have to have Access installed for it to work; if you can do anything in DAO, you can do everything in SQL. You don't even need Access on your own computer, but it's nice to have for building SQL statements using the query editor.

[wolf99]

Ok I have my code out but my filtering doesnt seem to be having the desired effect, even the first straightforward filter.
I'm not used to working with snapshots as opposed to regular recordsets so if anyone could give me a hand on that would be great [vb6; DAO]

heres what I have:

Sub showDataReport()
Dim count As Integer
For Index = 12 To 16
    count = getGroupQuery(REPAIR_FORM.fault(Index).Caption)
    For I = 1 To count
        If (queryCount(I) <> "") Then
            DataReport1.Sections("Section1").Controls.Item("Label1").Caption = queryCount(I) & "  " & I & "  " & REPAIR_FORM.fault(Index).Caption & vbCrLf
        End If
    Next I
Next Index
DataReport1.Refresh
DataReport1.Show
End Sub

Function getGroupQuery(faultName As String) As Integer
Dim count As Integer
Dim total As Integer
Dim scrapPartNum As String
Dim scrapFault As String
Dim numDone As Boolean
Set Repaired = OpenDatabase(Repaired_Loc)
Set Repaired_rs = Repaired.OpenRecordset("RepairData", dbOpenSnapshot)
Set repPartNum = Repaired_rs("Part Number")
Set repFault = Repaired_rs("Fault")

numDone = False
ReDim queryCount(0)
scrapFault = faultName

If Repaired_rs.BOF = True And Repaired_rs.EOF = True Then
    count = 0
Else
    Repaired_rs.MoveFirst
    Do
        If (scrapFault = repFault) Then
            Repaired_rs.Filter = (repFault = scrapFault)
            Do
                Repaired_rs.MoveFirst
                scrapPartNum = repPartNum
                count = UBound(queryCount)
                For I = 0 To count
                    If queryCount(I) = scrapPartNum Then
                    numDone = True
                    Repaired_rs.MoveNext
                        Exit For
                    End If
                Next I
                If numDone = False Then
                        Repaired_rs.Filter = ((repFault = scrapFault) And (repPartNum = scrapPartNum))
                        Repaired_rs.MoveLast
                        total = Repaired_rs.RecordCount
                        If (count < total) Then
                            ReDim Preserve queryCount(0 To total)
                        End If
                        queryCount(total) = scrapPartNum
                        count = UBound(queryCount)
                End If
                If (Repaired_rs.EOF()) Then
                    Exit Do
                    Exit Do
                End If
            Loop
        End If
        Repaired_rs.MoveNext
        If (Repaired_rs.EOF()) Then
            Exit Do
        End If
    Loop
End If
Repaired_rs.Close
Repaired.Close
getGroupQuery = count
End Function

The filtering doesnt seem to take effect properly at all, have I implemented it wrong?
also the program crashes somewhere after this (well, it stops and freezes the machine...)

[wolf99]

I think the .MoveFirst after I use the first filter, moves me back to the start of the snapshot instead of the filtered portion.....should this be done differently as I need at lest MoveLast for the RecordCount to work....

Also how is best to clear filtering effects>?

[wolf99]

Oh hey
just seen the posts above relating to SQL, sounds good I was looking at it breifly while looking at build queries for a access report, one thing If i have a string like you suggest;

SELECT * FROM MyTable WHERE ((field1 = string1) AND (field2 = string2));", dbOpenDynaset)

How do i find the number of records this query returns?

will chek that tut, thanks

[Hack]

Dim rs As Recordset
Dim sSQL As String
sSQL = "SELECT * FROM MyTable WHERE ((field1 = string1) AND (field2 = string2));", dbOpenDynaset) "
Set rs = db.OpenRecordset(sSQL)
Msgbox rs.RecordCount

Were db is your database object. Change to what you are using.

[Ellis Dee]

Wow, your code is kinda all over the place. Nesting logic that deep is often a sign that it can be re-written more efficiently.

Quickly, I'm not sure if you can apply filters to Snapshots. Try dbOpenTable instead. Also, I'm not a fan of the field objects; I'd recommend using the bang (!) operator instead. So instead of...

Set repPartNum = Repaired_rs("Part Number")
Set repFault = Repaired_rs("Fault")
...
If (scrapFault = repFault) Then
...
Repaired_rs.Filter = ((repFault = scrapFault) And (repPartNum = scrapPartNum))

...you can do this:

' No field objects needed
...
If (scrapFault = Repaired_rs!Fault) Then
...
Repaired_rs.Filter = ((repFault = scrapFault) And (Repaired_rs![Part Num] = scrapPartNum))

Note the square brackets around [Part Num]; they are needed when the field name has spaces or it is a reserved word.

Generally speaking, the code you posted would be much better off using SQL to filter the recordsets. Much of the conditional branching could be removed that way.

As for your SQL question, the standard approach is:

Dim db As Database
Dim rst As Recordset
Dim lngCount As Long
 
Set db = OpenDatabase("C:\MyDatabase.mdb")
Set rst = db.OpenRecordset("SELECT * FROM MyTable WHERE ((field1 = string1) AND (field2 = string2));", dbOpenDynaset)
If Not rst.EOF Then
    rst.MoveLast
    rst.MoveFirst
    lngCount = rst.RecordCount
End If
' Now loop through database...
Do While Not rst.EOF
    rst.MoveNext
Loop

[Ellis Dee]

Dim rs As Recordset
Dim sSQL As String
sSQL = "SELECT * FROM MyTable WHERE ((field1 = string1) AND (field2 = string2));", dbOpenDynaset) "
Set rs = db.OpenRecordset(sSQL)
Msgbox rs.RecordCount

Were db is your database object. Change to what you are using.

heh, you must be spoiled from ADO. DAO doesn't fully populate recordsets when you open them; you have to move to the last record to populate them.

This was "a feature, not a bug," as it supposedly makes it faster to just open a recordset and plow through each record if you didn't care how many there are ahead of time.

[wolf99]

thanks for putting up with my dense questioning and out of control branching
have projects piling up in new work placement and have never even touched vb before I started here only C, so I really appreciate your guidance.

[Al42]

heh, you must be spoiled from ADO. DAO doesn't fully populate recordsets when you open them; you have to move to the last record to populate them.

This was "a feature, not a bug," as it supposedly makes it faster to just open a recordset and plow through each record if you didn't care how many there are ahead of time.

Due, most likely, to its using a server-side cursor. ADO works the same way with a server-side cursor.

[wolf99]

Ok so code is now:

Function getQuery (faultName As String) As Integer
Dim db As Database
Dim rst As Recordset

Set db = OpenDatabase ("C:\Mydb.mdb")
Set rst = db.OpenRecordset ("SELECT * FROM Table WHERE (Field = faultName);", dbOpenSnapshot)
.
.
etc

at the SQL line, i think I got the (Field = faultName) wrong. is it here I should use the bang and square brackets??
I get an error: 3061 too few parameters. Expected 1

actually just tried using
Set rst = db.OpenRecordset ("SELECT * FROM Table WHERE (rst![Field] = faultName);", dbOpenSnapshot)
now but got the smae error but with Expected 2 !!

[Ellis Dee]

Set rst = db.OpenRecordset ("SELECT * FROM Table WHERE (Field = faultName);", dbOpenSnapshot)

Your suspicion is correct. Substitute the actual field name for Field.

I notice that you didn't use the query builder to make the SQL statement. I would recommend it, as it ensures the SQL statement will be in the ideal format for DAO. Here is a quick, small sample of a query SQL statement I just threw together in the query builder. I copied this code by selecting (from inside the query builder's top menu) View => SQL View

SELECT tblItem.*, tblItem.ItemID
FROM tblItem
WHERE (((tblItem.ItemID)=101605));

The idea is that you get the query working perfectly before porting the SQL statement over. Then copy it to the OpenRecordset line, removing the line feeds manually. Then substitute the literal values for your variables. The above would end up looking like:

Set rst = db.OpenRecordset("SELECT tblItem.*, tblItem.ItemID FROM tblItem WHERE (((tblItem.ItemID)=" & lngItemID & "));", dbOpenSnapshot)
If rst.EOF Then Msgbox "Item not found"
rst.close

Due, most likely, to its using a server-side cursor. ADO works the same way with a server-side cursor.

Thanks for the explanation; makes perfect sense.

[Hack]

Also, unless the name of your database table is Table, that needs to be changed as well.

[wolf99]

Ok that query builder really loses me, I havent a clue what most of it means.
Let me explain exectly what Im trying to do to try and get a clearer pic

I have a db table with several columns, one of which is "Fault" and one is "Part Num". First I have to select records that fall with in a group of Fault values. then find the quantity off a part number per fault

so I can enter to a report label

part number A was replaced x times for y fault
part number A was replaced z times for j fault
part number F was replaced h times for y fault

I have approx 2000 part numbers. my group has about 5 different faults.
so I though filter for first fault
then get first part number in the results then check if its been counted
already
if it hasnt check throught the results to see how maney times that part
number has that fault
move to the next part number and repeat
move to the next fault and repeat

[wolf99]

aha getting somewhere slowley, hows about
SELECT * From RepairData WHERE (((RepairData.Fault) = faultString));", dbOpenSnapshot

Is it possible to open two recordsets like this at the same time?

[wolf99]

Okk so now I have

("SELECT * FROM RepairData WHERE RepairData.Fault = '" & faultName &"';", dbOpenSnapshot)

I was getting errors as beofre for wrong number of conditions and that I either had too many or not enuf brackets. I removed the brackets alltogether and used the '"& &"' for the passed string, as in the FAQ tutorial , now I have a Runtime type mismatch error!!
If I output the string to the immediate window I get

SELECT * FROM RepairData WHERE RepairData.Fault = 'Dry Joint';

which is correct in this case, if I paste this into the query builder it loves it, so why the prob in vb?

(If I actually remove the Dim rst1 as Recordset, it solves it.....)

[Ellis Dee]

Oh, my bad, I thought you already knew how to use the query builder.

[Ellis Dee]

(If I actually remove the Dim rst1 as Recordset, it solves it.....)

Sounds like you have a reference to Activex Data Objects in your project. Get rid of that. (Project => References) ADO has its own recordset objects which conflict with DAO recordset objects.

[wolf99]

no, sorry, haven't had the luxioury of sitting down and looking at what Im doing and exploring alternatives really, just trying to get it done asap


I have however had the problem before that I get errors when recordsets or fields are declared instead of being left as variants, strange huh?

I have my first query working, with the one criteria, the second one with two however persists with datatype mismatch in expression error (3464).

it currently is

(SELECT * FROM RepairData WHERE RepairData.Fault = '" & faultName & "' And RepairData.[Part Number] = '" & partNum & "';", dbOpenSnapShot)

the fault is with second criteria in the imediate window the variable value comes up blank even though when I check its value by rolling over it in the code window It shows the correct value....
hmmm...

[wolf99]

Sounds like you have a reference to Activex Data Objects in your project. Get rid of that. (Project => References) ADO has its own recordset objects which conflict with DAO recordset objects.

Ummm, I am using DAO not ADO, or I though I was....

have done with the rest of the project I think

EDIT: solved the mismatch, stoopid me forgot to change one of the db fields to text from where I previously had it set to number. dur.

[Ellis Dee]

Don't put the single quotes around part number if it's numeric.

[wolf99]

ah well my edit to last post solved that, ta anyhoo.

about the ADO vs DAO, will I need change other code if I been using DAO (supposedly) so far? all of my db connections are set up in code instead of using a data control, but my report does use a data connection(If I ever get round to doing it), does this help or hinder If i was to remove the ActiveX and say I m using ADO?

[Ellis Dee]

It's not actually a problem to use both ADO and DAO in the same project. It's just that you normally don't explicitly reference libraries when declaring objects, so it's easy to run into weird conflicts. Also, ADO is the default reference in Access databases, so if you're using Access VBA and want to use DAO, it's generally a good idea to turn off ADO.

Sounds like you're in VB6 as opposed to Access VBA, though. If you have an ADO data-bound control (yuck) then you need both.

I believe you can simply explicitly reference the libraries during your declarations, similar to (but possibly not exactly like):

Dim db As DAO.Database
Dim rst1 As DAO.Recordset

Dim cn As ADODB.Connection
Dim rst2 As ADODB.Recordset

[wolf99]

Ok ta, ellis dee , will have to sprnd a litlle more time looking at this in a bit more detail me thinks

currently I think i actually have my SQL queries working (Yay); but I have error: no current record when trying to movenext, last or anywhere else after setting the recordset; maybe cos I removed the dim rs as recordset, hmmm...

anyway; code currently is:

Function getQuery(faultName As String) As Integer
Dim db As Database

Set db = OpenDatabase(Repaired_Loc)
Set rst1 = db.OpenRecordset("SELECT * FROM RepairData WHERE RepairData!Fault = '" & faultName & "';", dbOpenSnapshot)
rst1.MoveLast
rst1.MoveFirst
Do....
....
etc

[wolf99]

ah my bad, I not so awake this morning, apologies.

forgot to test for the sql returning no results. duh.

[wolf99]

So I think thats my original question resolved; here's the final code from the function, I am still having slight problems but I think its more to do with the report, so will start a thread in the report forum.

Function getQuery(faultName As String) As Integer
Dim db As Database
Dim count As Long
Dim partNum As String
ReDim queryCount(0 To 1)

Set db = OpenDatabase(Repaired_Loc)
Set rst1 = db.OpenRecordset("SELECT * FROM RepairData WHERE RepairData!Fault = '" & faultName & "';", dbOpenSnapshot)
If rst1.EOF = False And rst1.BOF = False Then
    rst1.MoveLast
    rst1.MoveFirst
    Do
        partNum = rst1![Part Number]
        For I = 0 To (UBound(queryCount))
              If queryCount(I) = partNum Then
                   numDone = True
                   Exit For
              End If
        Next I
        If numDone = False Then
            Set rst2 = db.OpenRecordset("SELECT * FROM RepairData WHERE RepairData!Fault = '" & faultName & "' And RepairData![Part Number] = '" & partNum & "';", dbOpenSnapshot)
            rst2.MoveLast
            rst2.MoveFirst
            count = rst2.RecordCount
            'strore in array
            If (UBound(queryCount)) < count Then
                ReDim Preserve queryCount(count)
            End If
            queryCount(count) = partNum
            rst2.Close
            Set rst2 = Nothing
        End If
        rst1.MoveNext
        If rst1.EOF Then
            Exit Do
        End If
    Loop
End If
rst1.Close
Set rst1 = Nothing
db.Close
getQuery = count
End Function

Muchos gracias for everybodies help, kudos all round