How to transfer data from one table to another based on date

[MOCaseA]

Greetings (again)...

This should be a simple matter but I cannot seem to find any information on how to do/build this.

Inside a single DB I have two tables... One is titled Employee Submissions and the other is Hours Archive.

What I need is a way to transfer records from the Employee Submissions table to the Hours Archive table based on the date associated with the field. Specifically I need any record older than 2 months to be transfered to the Hours Archive table. I also want the records in the Employee Submissions table to be deleted after the transfer.

Any thoughts?

[Mariostg]

This should get you started:

INSERT INTO [Destination Table] ( Field1, Field2, recordDate )
SELECT T.Field1, T.Field2, T.recordDate
FROM [Employee Submissions] T
WHERE (((T.recordDate)<=DateAdd("m",-2,Date())));
 

[MOCaseA]

Every time I edit it with the specific information it comes back with an "Expected" error.

Here are the table Names:
Employee Submissions
Hours Archive

These are the fields (they are the same for both tables):
empName
Direct Hours
Indirect Hours
Other Hours
entDate
Other Details
Presence

this is the gist of the code I derived:

Private Sub Form_Open(Cancel As Integer)
INSERT INTO [Hours Archive] (empName, Direct_Hours, Indirect_Hours, Other_Hours, entDate, Other_Detail, Presence) 
SELECT T.empName, T.Direct_Hours, T.Indirect_Hours, T.Other_Hours, T.entDate, T.Other_Detail, T.Presence
FROM [Employee Submissions] T
WHERE (((T.entDate)<=DateAdd("m",-2,Date())));
 
End Sub

The error is "Expected: end of statement" and highlights [Hours Archive]

[Mariostg]

I am sorry, every time you edit what? And what do you mean my specific information?
Did you adapt the query to your needs?

[MOCaseA]

Sorry... Every time I open the form (Event Procedure) I get the error. Above is the adapted (at least what I derived and adapted) code.

As I have mentioned before, I'm not very good with VB, but there is no one else here familiar with Access to build this DB.

[Mariostg]

Of course, if you want to use this through a form it is not going to work as is. This will work if you paste it in the SQL editor of the query designer. Then you can save it and run it as a query.

If you want to use it in a form action event, go like so:

Private Sub Form_Open(Cancel As Integer)
dim SQL as String
SQL = "INSERT INTO [Hours Archive] (empName, Direct_Hours" & _
    ", Indirect_Hours, Other_Hours, entDate, Other_Detail, Presence)" & _
    " SELECT T.empName, T.Direct_Hours, T.Indirect_Hours" & _
    ", T.Other_Hours, T.entDate, T.Other_Detail, T.Presence" & _
    " FROM [Employee Submissions] T" & _
    " WHERE (((T.entDate)<=DateAdd('m',-2,Date())));"
DoCmd.RunSQL SQL
End Sub
 

[MOCaseA]

Quick question... Will this string require a SQL server or SQL services to be running? If so, neither of them are running on this system.

When I try to execute this code it gives me the following error:

Run-time error '3134':

Syntax error in INSERT INTO statement.

When I run the debugger it highlights the DoCmd.RunSQL SQL Line (I'm assuming this is because this is the actual command that initiates the code)

[Mariostg]

What about your field names? Are they like Indirect Hours or Indirect_Hours?
If there is a space, you must enclose them in bracket : [Indirect Hours]

[MOCaseA]

Never mind... I just got laid off so no more working on this DB... sorry to be such a bother...