Filtering a DAO Recordset

[Welshgasman]

If (strCurClassID >= 2 And strCurClassID <= 5) Then
    rsEMAs.Filter = "ClassID = " & strCurClassID
End If
Set rsEMAsFltrd = rsEMAs.OpenRecordset

[moke123]

What about something like:

If (strCurClassID >= 2 And strCurClassID <= 5) Then

      strSql = "Select * from sometable where ClassID = " & strCurClassID

Else

      strSql = "Select * from sometable

End If

    Set db = CurrentDb()
    Set rs = db.OpenRecordset(strSql)

[GraeagleBill]

This might work if one could specify the domain with query rather than a table.

If (strCurClassID >= 2 And strCurClassID <= 5) Then

      strSql = "Select * from SomeQuery where ClassID = " & strCurClassID
Else
      strSql = "Select * from SomeQuery 


End If

    Set db = CurrentDb()
    Set rs = db.OpenRecordset(strSql)

[Welshgasman]

This might work if one could specify the domain with query rather than a table.

If (strCurClassID >= 2 And strCurClassID <= 5) Then

      strSql = "Select * from SomeQuery where ClassID = " & strCurClassID
Else
      strSql = "Select * from SomeQuery 


End If

    Set db = CurrentDb()
    Set rs = db.OpenRecordset(strSql)

What is to there to stop you?

[Minty]

Just to reinforce what micron and WGM said - the source can be a table or a stored query.

[moke123]

Just to reinforce what micron and WGM said - the source can be a table or a stored query.

You could also probably finagle a querydef too, but my whole point was I think you were over complicating the procedure to begin with.

[GraeagleBill]

I tried the following even before the OP:

strSQL = "SELECT * FROM QName WHERE ClassID = " & strCurClassID


Set rsEMAs = DBEngine(0)(0).OpenRecordset(strSQL)

AND

strSQL = "SELECT * FROM " & QName & " WHERE ClassID = " & strCurClassID


Set rsEMAs = DBEngine(0)(0).OpenRecordset(strSQL)

AND

strSQL = "SELECT * FROM QEMA-ALL WHERE ClassID = " & strCurClassID


Set rsEMAs = DBEngine(0)(0).OpenRecordset(strSQL)

I followed the string value of strSQL in debug and it seemed to be okay, but after Access complained about the FROM clause, I switched to a different approach that ultimately led to the OP.

[Welshgasman]

Well the first attempt is never going to work , but I cannot see anything wrong with the other two.?

[GraeagleBill]

So, I returned to the strSQL SELECT approach and finally found that Access simply didn't like the way the 18-year old query was named. Since I've long since stopped using any kind of "-", "_" or anything else in naming objects and variables, it just didn't occur to me right off that the way the "old" query was named was the source of the whole approach. All I had to do was rename the query.

strSQL = "SELECT * FROM QEMAll WHERE ClassID = " & strCurClassID


Set rsEMAs = DBEngine(0)(0).OpenRecordset(strSQL)

I'm still puzzled why the approach posted in #16 didn't work, but all I got returned in the rs was a count of "1".

Thanks guys for putting up with this "Old Geezer", I truly appreciate your efforts.
Bill

[Welshgasman]

Well I have never used - or _, but - does not work for me. I get that From error message. However _ works fine for me.?

I know a hypen is frowned upon in DB development, and an underscore used instead. Perhaps that is why.?

Oh well, something new learnt today.

[Minty]

If you think about it Access is probably going to evaluate QEMA-ALL as

QEMA Minus ALL

Which may well cause an issue or two...

[Welshgasman]

Unless you movelast, you will always get that, unless the recordset is empty?

[Micron]

If you think about it Access is probably going to evaluate QEMA-ALL as

QEMA Minus ALL

Which may well cause an issue or two...

Experienced that a long time ago. Must be [QEMA-ALL] not QEMA-ALL. QEMA_ALL ok too - probably better, right up there with Camel Case ( QEMAall or similar).

[GraeagleBill]

Now that all the total conditions have been addressed, here's what the code looks like:

Dim I As Integer
Dim strSQL As String
Dim strAppend As String


strSQL = "SELECT * FROM QEMAll"                           'Start by assuming everyone
strAppend = ""
    
If QName = "QSelected" Then _
    strAppend = " WHERE SelAro <> "" """                  'Individuals selected (blanks are explicit)


If (strCurClassID >= 2 And strCurClassID <= 5) Then       'Filter class? strCurClassID (form global)
    If strAppend = "" Then
        strAppend = " WHERE ClassID = " & strCurClassID   'Yes, the whole class
    Else
        strAppend = " WHERE ((SelAro <> "" "") AND (ClassID = " & strCurClassID & "))"   'Selects within class
    End If
End If


If strAppend <> "" Then strSQL = strSQL & strAppend       'Some filtering, compound or otherwise
    
Set rsEMAs = DBEngine(0)(0).OpenRecordset(strSQL)         'rsEMAs Recordset global to form module


intEMACount = rsEMAs.RecordCount

[Welshgasman]

Still not going to get correct record count?