Hi All,
I have a VBA script when run, its supposed to delete records that are more than 7 years old based on their max date.
It pops up with a question "you are about to delete 0 records from 0 fields from the specified table"
I know this isnt an error message, but for some reason it doesnt seem to be attempting to delete any records, even with the date in 2012, the select query shows all the data to be purged (which is a seperate item all together)
here is the new updated code:
------
Private Sub mybutton12355_Click()
Set Db = CurrentDb()
Dim strSQL As String
'
strSQL = "DELETE FROM RecTrans WHERE " & _
"ContractNumber IN SELECT " & _
"RECTRANS.ContractNumber " & _
"FROM RECTRANS " & _
"GROUP BY RECTRANS.ContractNumber " & _
"HAVING (((DateAdd('yyyy',7,Max([RECTRANS].[TransactionDate])))<Now())));"
'
strSQL = "DELETE FROM ADV WHERE ContractNumber NOT IN (Select ContractNumber " & _
"FROM Rectrans);"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
strSQL = "DELETE FROM CBL WHERE ContractNumber NOT IN (Select ContractNumber " & _
"FROM Rectrans);"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
strSQL = "DELETE FROM LEASE WHERE ContractNumber NOT IN " & _
"(Select ContractNumber FROM Rectrans);"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
strSQL = "DELETE FROM PL WHERE ContractNumber NOT IN " & _
"(Select ContractNumber FROM Rectrans);"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
strSQL = "DELETE FROM HP WHERE ContractNumber NOT IN " & _
"(Select ContractNumber " & _
"FROM Rectrans);"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
Set Db = Nothing
End Sub
------
Does anyone have any ideas?
Thanks to RoyVidar for all his help.
I have a VBA script when run, its supposed to delete records that are more than 7 years old based on their max date.
It pops up with a question "you are about to delete 0 records from 0 fields from the specified table"
I know this isnt an error message, but for some reason it doesnt seem to be attempting to delete any records, even with the date in 2012, the select query shows all the data to be purged (which is a seperate item all together)
here is the new updated code:
------
Private Sub mybutton12355_Click()
Set Db = CurrentDb()
Dim strSQL As String
'
strSQL = "DELETE FROM RecTrans WHERE " & _
"ContractNumber IN SELECT " & _
"RECTRANS.ContractNumber " & _
"FROM RECTRANS " & _
"GROUP BY RECTRANS.ContractNumber " & _
"HAVING (((DateAdd('yyyy',7,Max([RECTRANS].[TransactionDate])))<Now())));"
'
strSQL = "DELETE FROM ADV WHERE ContractNumber NOT IN (Select ContractNumber " & _
"FROM Rectrans);"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
strSQL = "DELETE FROM CBL WHERE ContractNumber NOT IN (Select ContractNumber " & _
"FROM Rectrans);"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
strSQL = "DELETE FROM LEASE WHERE ContractNumber NOT IN " & _
"(Select ContractNumber FROM Rectrans);"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
strSQL = "DELETE FROM PL WHERE ContractNumber NOT IN " & _
"(Select ContractNumber FROM Rectrans);"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
strSQL = "DELETE FROM HP WHERE ContractNumber NOT IN " & _
"(Select ContractNumber " & _
"FROM Rectrans);"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
Set Db = Nothing
End Sub
------
Does anyone have any ideas?
Thanks to RoyVidar for all his help.
