Help Using VBA Query 1

[egstatus]

Hi,
I have this query in access:

UPDATE PAYROLL SET PayRollToDate = CDate( Format$([PayRollToDate], "MM/DD/") & "2012" )
WHERE Year([PayRollToDate]) = 2010;

It runs fime from the query object. Now I want to use it in VBA, I have about 75 tables And I want to use code to do the update. All it does is, it changes the year part of the PayRollToDate field from 2010 to 2012.

I have tryed the following.

Function UpdateMpayTables()
    Dim db As Database
    Dim tdf As TableDef
    Dim currentdb As Database
    Dim strSQl As String
        
    Set currentdb = Application.currentdb
     
    'For Each tdf In db.TableDefs
    For Each tdf In currentdb.TableDefs
        If (Left(tdf.Name, 4)) = "MPAY" Then
            If Len(Trim(tdf.Name)) = 8 Then
                lngRecordsInTable = lngRecordsInTable + tdf.RecordCount
                DoCmd.SetWarnings False
                 
                 [highlight #EF2929] This is where by problem is[/highlight]                 
                 'Year Field to Update
                 strSQl = "UPDATE " & tdb.name & _
                 " SET PayRollToDate = # " & CDate(Format$(tdf.Name &".PayRollToDate" &, "MM/DD/") & "2012") & "#" & _
                 " WHERE Year([PayRollToDate]) = 2010;"
                             
                 DoCmd.RunSQL strSQl
                 intTableUpdated = intTableUpdated + 1
             End If
        End If
    Next tdf
End Function

Can someone please help me?

Thanks

Ed

 

[dhookom]

I think this might work:

strSQl = "UPDATE " & tdb.name & _
" SET PayRollToDate = DateAdd('yyyy',2, PayRollToDate)" & _
" WHERE Year([PayRollToDate]) = 2010"

This assumes you don't have any spaces in your table names.

Duane
Hook'D on Access
MS Access MVP

 

[egstatus]

Thanks very much.