Where is the error?

[snipe]

Private Sub NewRunMontage_Click()
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
        Set db = CurrentDb()
        Set qdf = db.QueryDefs("Q_Bucket_Matt")
    
    Select Case Me.WhichMontage
        Case 1
            strSQL = "SELECT DISTINCT MERCH_INV_MNR.MNR_CD FROM MERCH_INV_MNR;"
            qdf.SQL = strSQL
        Case 2
            strSQL = "SELECT DISTINCT MERCH_INV_MNR.MNR_CD FROM MERCH_INV_MNR " & _
                    "WHERE (((MERCH_INV_MNR.MNR_CD) Not In ('8300','8310','8413','8500,'8501','8502','8503','8504','8900','8510','8900','8700')));"
            qdf.SQL = strSQL
        Case 3
            strSQL = "SELECT DISTINCT MERCH_INV_MNR.MNR_CD FROM MERCH_INV_MNR " & _
                    "WHERE (((MERCH_INV_MNR.MNR_CD)='8300' Or (MERCH_INV_MNR.MNR_CD)='8310' Or (MERCH_INV_MNR.MNR_CD)='8413' " & _
                    "Or (MERCH_INV_MNR.MNR_CD)='8500' Or (MERCH_INV_MNR.MNR_CD)='8501' Or (MERCH_INV_MNR.MNR_CD)='8502' " & _
                    "Or (MERCH_INV_MNR.MNR_CD)='8503' Or (MERCH_INV_MNR.MNR_CD)='8504' Or (MERCH_INV_MNR.MNR_CD)='8900' " & _
                    "Or (MERCH_INV_MNR.MNR_CD)='8700'));"
            qdf.SQL = strSQL
    End Select
    Set db = Nothing
    Set qdf = Nothing
    
End Sub

Case 1 and 3 work great. case 2 gives me a syntax error.

Can't figure out what is wrong. I created the 3 different cases with the query builder, then just brought them over to the VBA.

[June7]

I just tested that syntax and it works.

8900 is duplicated in the set.

Don't repeat the qdf.SQL = strSQL within the SELECT CASE. It should be outside the Select.

[snipe]

Its a text field. Guess you just edited your post.

I'll pull out that duplicate 8900, thanks for noticing that.

That worked for you as is? Or moving the qdf.SQL=strSQL out of the select fixed it?

[ssanfu]

I don't know if you have fixed it , but there is a missing single quote

Not In ('8300','8310','8413','8500,'8501','8502','8503','8504','8900','8510','8900',' 8700')));"

Should be

Not In ('8300','8310','8413','8500','8501','8502','8503','8504','8900','8510','8900','8700')));"

I think that is what is causing the syntax error.

[June7]

Good eye, Steve!

My test of syntax was with my data, did not construct table similar to your data and so did not copy/paste your SQL and did not catch the missing apostrophe, although should have seen it. If you copy/pasted from Access query, that query should have failed.

My suggestions would not fix the issue but were just better coding.

[snipe]

Perfect! Thanks Steve! June, I moved the qdf line out of the select as well. I appreciate the help, as always! Not sure how that apostrophe get omitted, oh well.

[snipe]

Ohhhhh going from design view to SQL view uses " (quotes) around all of the criteria, and VBA gave me an error so I switched them all to apostrophes. So it was my bad. Doh!