Issue with SQL in VBA

[JamesW]

Hi there,

I'm trying to set a value in a table based on a multi-select listbox. I have little to no experience of SQL.

Private Sub Command2_Click()
    Dim str_SQLText     As String, _
        str_MNVal       As String, _
        str_RTVal       As String
 
    Dim i               As Long
 
    With lst_MatSelection
        For i = 0 To .ListCount - 1
            If .Selected(i) Then
                str_RTVal = .Column(1, i)
                str_MNVal = .Column(2, i)
                str_SQLText = "UPDATE tbl_MaterialData " & _
                    "SET Complete = YES " & _
                    "WHERE Material Number = '" & str_MNVal & "' AND " & _
                    "Request Type = '" & str_RTVal & "'"
                DoCmd.RunSQL str_SQLText
            End If
        Next i
    End With
End Sub

I get a Syntax Error - Missing Operator.

Any ideas where I'm going wrong? Complete is a Yes/No field. I have also tried =True but I get the same error.

Cheers,

James

 

[revans]

This might not be the source of your troubles but you have spaces in your field names. Wrap the field name in brackets like this [Material Number]. Also, it's not clear if Material Number is a number or a string. If it's a number you don't want to wrap the variable in quotes as the sql will treat it as a string and you'll get other errors.

hth,

Rich

 

[JamesW]

Hi Rich,

Brackets sorted it for me.. Time to read up on ADO and SQL!

Cheers mate.

 

[Norie]

James

Try -1 instead of True.