Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2003
    Location
    Belfast, UK
    Posts
    87

    Unanswered: Refering to two different tables depending on date

    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

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Yes. In the Report_Open method pull the date and test it and set your RecordSource accordingly...

  3. #3
    Join Date
    Dec 2003
    Location
    Belfast, UK
    Posts
    87
    I'm sorry I don't know how to do this.

  4. #4
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Try 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
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  5. #5
    Join Date
    Dec 2003
    Location
    Belfast, UK
    Posts
    87
    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.

  6. #6
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Lightbulb Query

    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?
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •