[RESOLVED] nested filtering?

[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....

[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