Change query filters

[Jon Henry]

Im trying to change the query filter for a listboix using an options group with 3 buttons. Here is my code:

Private Sub optView_AfterUpdate()

    Dim strUpDate As String
    
    If Me.optView.Value = 1 Then
        strUpDate = "UPDATE ServReq Query WHERE (((ServReq Query[Status]=1) OR ((ServReq Query[Status]=2) OR ((ServReq Query[Status]=3) OR ((ServReq Query[Status]=4) OR ((ServReq Query[Status]=9);"
    ElseIf Me.optView.Value = 2 Then
        strUpDate = "UPDATE ServReq Query WHERE (((ServReq Query[Status]=5) OR ((ServReq Query[Status]=6) OR ((ServReq Query[Status]=7) OR ((ServReq Query[Status]=8);"
    Else
        strUpDate = "UPDATE ServReq Query WHERE (((ServReq Query[Status]=1) OR ((ServReq Query[Status]=2) OR ((ServReq Query[Status]=3) OR ((ServReq Query[Status]=4) OR ((ServReq Query[Status]=5) OR ((ServReq Query[Status]=6) OR ((ServReq Query[Status]=7) OR ((ServReq Query[Status]=8) OR ((ServReq Query[Status]=9)));"
    End If
    
    CurrentDb.Execute strUpDate, dbFailOnError
    
End Sub

The query is called ServReq Query. IM trying to show different status' with different options group buttons(IE btn 1 shows status' 1,2,3,4,9 -- btn 2 shows 5,6,7,8 -- btn 3 shows all)

How should my update statement be formatted?
Im getting 'Run-time error '3144'' Syntax error in UPDATE statement.

 

[pbaldy]

You've left out the SET clause. You would also have to bracket the query name due to the inadvisable space in the name. You could also use IN, which would make the SQL a little simpler.

 

[Jon Henry]

OK but IM not trying to set any actual values to any records, just change the filtering on the query. I still use a SET statement and if so, how do I use it to set nothing?

 

[Jon Henry]

Or would it be more useful to use a

SELECT [ServReq Query].Status WHERE Status = 1 etc..

Ive been using Access for short while but am a complete SQL novice.

 

[pbaldy]

Oh, I misunderstood. No, you don't want an UPDATE. Try this; leave the query with no filtering (or none on this field, as appropriate). Then do this:

If Me.optView.Value = 1 Then
  strUpDate = "SELECT * FROM [ServReq Query] WHERE Status IN(1, 2, 3, 4, 9)"
...

End If

Me.ListBoxName.RowSource = strUpDate

Does that look like what you're after?

 

[Jon Henry]

Looks like it may be what the doctor ordered. IM out for the day, I will give it a go tommorrow and see what happens.

Thanks pbaldy.

 

[pbaldy]

Good, let me know how it goes. BTW, for the last one that should show all, rather than adding a WHERE clause that lists all the options, simple drop the WHERE clause:

SELECT * FROM [ServReq Query]