Using Me.RecordSource

[JasonTheAdams]

Greetings!

I have the following sub:

Public Sub FilteredQuery()
    Dim Clauses(2) As String
    Dim SQL As String
    
    'Add columns here
    Call AppendClause(Clauses, 0, Me!chkShipped, "Shipped")
    Call AppendClause(Clauses, 1, Me!chkClosed, "Closed")
    Call AppendClause(Clauses, 2, Me!chkControl, "Controls ReqD")
    
    SQL = "SELECT * FROM 'Job Status'"
    If (UBound(Clauses) <> -1) Then
        SQL = SQL + " WHERE " + Join(Clauses, " AND ")
    End If
    
    Me.RecordSource = SQL
End Sub

SQL = "SELECT * FROM 'Job Status' WHERE 'Shipped' = true AND 'Closed' = true AND 'Controls ReqD' = true"

For some reason, though, when I run this I'm told that Me.RecordSet has invalid SQL in it. That looks like perfectly valid SQL to me. Someone see something I'm not?

Thanks!

 

[David R]

Try enclosing [Controls ReqD] in brackets, as your field name has a space in it. In fact all of those should be brackets, not single quotes.

I'm also not sure if SQL is a reserved word, you may want to change your variable name.

 

[JasonTheAdams]

Yup, I just learned that MS, in all of its wisdom, decided to replace single-quotes with square-brackets... Which is not SQL standard. That's annoying.

Otherwise, SQL isn't a preserved word. But this did fix the problem. Thanks!

 

[pr2-eugin]

I thought SQL was a reserved word, based on the KB article 'List of reserved words in Access 2002 and in later versions of Access'