Help with SQL statement: using VBA to update table (run-time error 3075)

[dpfaff]

Hi,



I use the following code to assign records by round robin in a few of my databases.

Option Compare Database
Option Explicit


Function RR_assignAL3_unsuccessful() As Boolean
'On Error GoTo Err_RR_assignAL3_unsuccessful


    Dim db As DAO.Database
    Dim rsSpecialists As DAO.Recordset
    Dim rsProcessing As DAO.Recordset
    Dim strSQL As String
    
   
    
    'Specialist assignments table
    strSQL = "Select Specialist, ID " & _
             "FROM ztblSpecialist " & _
             "WHERE Inactive = 0"
             
    Set db = CurrentDb()
    Set rsSpecialists = db.OpenRecordset(strSQL)
    
    'Select records
    strSQL = "Select * from tbl_AL3_unsuccessful " & _
              " WHERE Specialist IS NULL "
              
    
    Set rsProcessing = db.OpenRecordset(strSQL)
    
    If rsProcessing.RecordCount >= 1 Then
    
            rsSpecialists.MoveFirst
            rsProcessing.MoveFirst
            
            DoCmd.SetWarnings False
            
                Do While Not rsProcessing.EOF
                'Update records
                        strSQL = "UPDATE tbl_AL3_unsuccessful SET Specialist = '" & rsSpecialists.Fields("Specialist") & _
                                 "' WHERE Specialist IS NULL" & _
                                 " and POLICY NUMBER = " & rsProcessing.Fields("POLICY NUMBER")
                                 'FIELD REFERENCED IN LINE ABOVE MUST BE NUMBER FORMAT
                                 


                        DoCmd.RunSQL (strSQL)
                        rsProcessing.MoveNext
                    
                        rsSpecialists.MoveNext
                        If (rsSpecialists.EOF) Then
                            rsSpecialists.MoveFirst
                        End If
                Loop
                RR_assignAL3_unsuccessful = True
                    
    End If
             
Exit_RR_assignAL3_unsuccessful:
    DoCmd.SetWarnings True
    Set db = Nothing
    Set rsSpecialists = Nothing
    Set rsProcessing = Nothing
    Exit Function


Err_RR_assignAL3_unsuccessful:
    If Err.Number = 2501 Then
        Resume Next
    Else
        MsgBox Err.Description, _
            vbCritical, "<<<<Error....>>>>>"
        Resume Exit_RR_assignAL3_unsuccessful
    End If
End Function

Line 40 is causing a Run-time error 3075: Syntax error (missing operator in query expression).

" and POLICY NUMBER = " & rsProcessing.Fields("POLICY NUMBER")

The field referenced in line 40 is what determines when to assign multiple records to the same specialist (rather than assigning to the next specialist). Usually I use a field with a numeric value and it works fine. In this case, the assignment needs to be based by the POLICY NUMBER field, which is a text field.

Does anyone know how I can change the syntax for that line to work with POLICY NUMBER being a text field? I can't convert it to number because it is alpha-numeric.

Thanks,
Don

[June7]

Field name has space. Try enclosing in []. Also need apostrophe delimiters for text field parameters.

" and [POLICY NUMBER] = '" & rsProcessing.Fields("[POLICY NUMBER]") & "'"

or

" and [POLICY NUMBER] = '" & rsProcessing![POLICY NUMBER] & "'"


Advise not to use spaces or special characters/punctuation (underscore is exception) in naming convention. Better would be PolicyNumber or Policy_Number.

[dpfaff]

Thank you so much June, you're the best! I pasted your first line and that worked perfectly. I appreciate the help. I have so much to learn...

This is much preferable to my workaround, which was going to be to use Microsoft's RemoveAlpha's function to create a numeric version of the policy number.