DLookup Syntax error

[captdkl02]

I am receiving this error in regards to DLookup: Syntax error (missing operator) in query expression 'Course Number = 'FMF 1002"

This is line of code where error is happening:

CompetencyType = Nz(DLookup("Competency", TableContainUpdates, "[Course Number] = '" & rs.Fields(FieldtoPair) & "'"), "No Competency")

I have looked online and they have the same syntax that I have above for Course Number criteria.

Any suggestions with syntax to make the DLookup work??

Thank you.

I am including my entire subrountine.

Private Sub Update_FM_Certification_Table_Button_Click()
On Error GoTo Err_Update_FM_Certification_Table_Button_Click
Dim SPField As DAO.field
Dim rs As DAO.Recordset  ' table to update
Dim rs1 As DAO.Recordset ' this table is the source with the updates to update the other table
Dim curDB As DAO.Database ' this is the access database
Dim TabletoUpdate As String, TableContainUpdates As String   ' database table names
Dim strSQL As String, strSQL1 As String
Dim FieldtoPair As String ' user enters field to pair for table updates
Dim CompetencyType As String ' Competency type from table contains updated course information
Dim CreditHrs As Double ' Course Credit hours from table contains updated course information

TabletoUpdate = Me.MyTabletoUpdate   ' Assign table name to update from user input
TableContainUpdates = Me.MyTableContainUpdates  ' Assign table name that contains the updates from user input
FieldtoPair = Me.Pair_Field 'Assign field name that is common to pair for table updates
Set curDB = CurrentDb()
' Select table to update and order by course number
    strSQL = "SELECT * FROM [" & TabletoUpdate & "] ORDER BY [" & FieldtoPair & "];"
    Set rs = curDB.OpenRecordset(strSQL)
    
' Select table contains the updated info and order by course number
    strSQL1 = "SELECT * FROM [" & TableContainUpdates & "] ORDER BY [" & FieldtoPair & "];"
    
    Set rs1 = curDB.OpenRecordset(strSQL1)
    
        Do Until rs.EOF ' Looping through DB table to update
' **** Debug code ****
'          If rs.Fields(FieldtoPair) = "FMF 1302" Then
'                MsgBox "FMF 1302"
'          End If
' **** Debug code ****
' Do loop read table contains update info record until EOF
            Do Until rs1.EOF   ' Looping through DB table that contains the updated information
                For Each SPField In rs1.Fields
                    If (SPField.Name = "Competency") And (rs.Fields(FieldtoPair) = rs1.Fields(FieldtoPair)) Then
                        CompetencyType = Nz(DLookup("Competency", TableContainUpdates, "[Course Number] = '" & rs.Fields(FieldtoPair) & "'"), "No Competency")
                        CreditHrs = Nz(DLookup("CREDIT_HRS", TableContainUpdates, _
                                      "Course Number = '" & rs.Fields(FieldtoPair) & "'"), 0)
                        rs.Edit
                        rs.Fields(CompetencyType) = CreditHrs
                        rs.Update
                    ElseIf ((SPField.Name <> "CREDIT_HRS") And (SPField.Name <> "Competency")) Then
                        If ((Len(Nz(rs.Fields(SPField.Name), "")) = 0) And (Len(Nz(rs1.Fields(SPField.Name), "")) <> 0) And (rs.Fields(SPField.Name) <> rs1.Fields(SPField.Name))) And (rs.Fields(FieldtoPair) = rs1.Fields(FieldtoPair)) Then
                            rs.Edit
                            rs.Fields(SPField.Name) = rs1.Fields(SPField.Name)
                            rs.Update
                        End If
                    End If
                Next SPField
            
            rs1.MoveNext
            Loop ' EOF rs1 loop
            Set rs1 = curDB.OpenRecordset(strSQL1)
          rs.MoveNext
        Loop ' EOF rs loop
'
Exit_Update_FM_Certification_Table_Button_Click:
    Exit Sub
Err_Update_FM_Certification_Table_Button_Click:
    MsgBox Err.Description
    Resume Exit_Update_FM_Certification_Table_Button_Click
   On Error GoTo 0
   Exit Sub
Update_FM_Certification_Table_Button_Click_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Update_FM_Certification_Table_Button_Click of VBA Document Form_Prototype form"
End Sub

 

[pbaldy]

I think it's the one after, not that one. The one after doesn't have brackets around the field name, required due to the inadvisable space.