Help Writing Query in VBA

[egstatus]

Hi every one, I have the following query that I created using the query designer in access, I would like to use in in my VBA code:
UPDATE 2JMEDTEST SET [2JMEDTEST].BATCH = Trim([BATES]) & "_" & Trim([Invoice Number]) & "_" & Trim([Invoice Date]);

I can't get the query to work in vba, I tried couple of different things without much luck.
What Am I doing wrong here?

Public Function RenameImageField(strTableName As String) As Boolean
    'I pass the table name as parameter because the table name will be different but the fields will remain the same 
    On Error GoTo errhandler
    Dim strSql As String, DB As DAO.Database
   
    'Update the BATCH field to the content of the [BATES] + "_" + [Invoice Number] + "_" + "Invoice Date]
    [COLOR=#3465A4]'This is what I need help with[/color]
    [COLOR=#EF2929]strSql = "UPDATE " & strTableName & " SET " & strTableName & ".BATCH = '" & Trim([BATES]) & " & ""_"" & " Trim([Invoice Number])" & "_" & " Trim([Invoice Date]) & " ';"[/color]
    
    Debug.Print strSql
     
    'Use Current Database
    Set DB = currentdb()

    'Run the SQL Query
    DB.Execute strSql

   'If no errors return true
   RenameImageField = True
ExitHere:
   Set DB = Nothing
   'Notify the user the process is complete.
   'MsgBox "Update Complete"
   Exit Function
errhandler:
   'There is an error return false
   RenameImageField = False
   With Err
      MsgBox "Error " & .Number & vbCrLf & .Description, _
            vbOKOnly Or vbCritical, "updateField"
   End With
   Resume ExitHere

End Function

Thanks in advance

Ed

 

[SkipVought]

Hi,

If you COPY the string from the Debug.Print in the Immediate Window and PASTE it into the GUI SQL Editor, does THAT code produce the correct results?

 

[dhookom]

What are the results of Debug.Print or did you even get that far?

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

Try:

strSql = "UPDATE " & strTableName & " SET " & strTableName & _
        ".BATCH = '" & Trim([BATES]) & " & ""_"" & Trim([Invoice Number]) & ""_"" & Trim([Invoice Date]) "

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

I'd try this:

strSql = "UPDATE " & strTableName & " SET " & strTableName & ".BATCH = Trim([BATES]) & '_' & Trim([Invoice Number]) & '_' & Trim([Invoice Date]);"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886