Is it possible to create code so that when i run a query/report it can refer to two different tables depending on the date.

Example the user keys date the data is keyed, when running the report I want it to read from table A if the date keyed is before 01/01/04 and table B if the date keyed is After 01/01/04

Thanks

Yes. In the Report_Open method pull the date and test it and set your RecordSource accordingly...

I'm sorry I don't know how to do this.

Does the user key the date on a form then click print report?

IF so, dump this code in the report_open event:
(Change the formnamewithuserinput.fieldnamefromthisfor to correspond to your form and field / textbox names)

Private Sub Report_Open(Cancel As Integer)

If Forms!formnamewithuserinput.fieldnamefromthisform < #1/1/2004# Then
Me.RecordSource = "tablename"

Else
Me.RecordSource = "othertablename"

End If

End Sub

no when they key the original data the keyed the date the information was keyed, this is included in the report. What's happening is we are restructuring the company so that different outlet now fall in different areas. but for the reports we still need to be able to retain the old data as well as the new.

I have created a new table with the new network structure leaving the previous table intact.

Could the report contain data from both tables?

If so create a query to pool data from both tables and use the query as the recordsource for the report?