Array Parameter Invokes "Invalid Qualifier"

[JasonTheAdams]

So I have a sub, here:

Public Sub AppendClause(Clauses() As String, Item As CheckBox, Column As String)
    Select Case True
    Case IsNull(Item.Value)
        Return
    Case (Item.Value = -1)
        Clauses.Append ("'" + Column + "'" + "=true")
    Case (Item.Value = 0)
        Clauses.Append ("'" + Column + "'" + "=false")
    End Select
End Sub

The problem I'm having is that Clauses.Append causes a "Invalid Qualifier" error to happen. I know that error means it doesn't recognize anything within scope that has that name.. But I'm obviously defining it in the parameters.

Any ideas?

 

[pr2-eugin]

Sorry, but WHAT is Clauses.Append? You Select statement will always go to the second case.. i.e. True.. No matter what arguments you pass..

I am not sure if there you can use a Return statement in VBA..

 

[JasonTheAdams]

I apologize, I'm more familiar with VB than I am VBA.

Clauses is a string array, so Append is a inherited array method to place the passed string to a new element at the end of the array.

Why would the 2nd case happen every time?

Hmm.. Well, if that gives me an issue I'll just wrap the whole thing in an If statement instead.

Thanks!

 

[pr2-eugin]

I apologize, I'm more familiar with VB than I am VBA.

Clauses is a string array, so Append is a inherited array method to place the passed string to a new element at the end of the array.

Ah ! Makes more sense now.. Well if you want to Add elements on the fly, check out THIS link..

Why would the 2nd case happen every time?

I was wrong.. The Case will never execute.. Your case should look like..

    Select Case [Item].Value
        Case True
            [COLOR=Green]'CODE Here[/COLOR]
        Case Else
            [COLOR=Green]'CODE Here[/COLOR]
    End Select

 

[JasonTheAdams]

Huh.. Wow, I didn't realize VBA doesn't have an append method. That's unfortunate. But thanks for pointing to a workaround!

I've done the Select Case True all over the place. It's the logical equivalent as an If..elseif..elseif..end. Unless Select Case works in some strange way in VBA, that should work just fine. I've just done that in the past as I think it looks better than a bunch of elseif's.

I'm now having a new issue, though. Here's my code:

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

It works as far as what's in SQL, but when I pass it to Me.RecordSource the Watcher informs that Me.RecordSource is only "Job Status" -- after a "syntax error in query" error. Any ideas?

SQL is: "SELECT * FROM 'Job Status' WHERE 'Shipping' = true AND 'Closed' = true AND 'Controls ReqD' = true"

Normally I wouldn't give capital letters and spaces to column names.. but I didn't put this database together.

Thanks!

 

[JHB]

Try by changing the "true" to "-1" and use "[]" instead of "''", (single quotes).

SQL is: "SELECT * FROM [Job Status] WHERE [Shipping] = -1 AND [Closed] = -1 AND [Controls ReqD] = -1"