Returning data depending on date query help

[MissyEd]

Hi,

I have a table listing different offices as below:

Office_Number e.g 1, 2, 3
Company_Name e.g MyCompany Ltd, YourCompany Ltd
Trading_Name e.g Whizzy1 Computers, Quick Software Soln
Old_Office_Number e.g 9, 8
Old_Company_Name e.g OldCompany Ltd, TakenOver Ltd
Old_Trading_Name e.g 1-2-3 Computers, Gemini Software Soln
Date_Changed e.g 1-Apr-2000

What Im trying to do is allow a user to select a date for a report e.g Jan 2000. The reports are based on company names, so if a company existed before 1-Apr-2000 but is now defunct, I still need to be able to show a report for the expenditure of the old company & its branches. Occasionally, just the Office_Number changes.

In VBA I could build a recordset which would have the correct details e.g

If User_Selected_Report_Date < Date_Changed Then
Temp_List!Office_Number = Office_List!Old_Office_Number
Temp_List!Company_Name = Office_List!Old_CompanyName
etc
Else
Temp_List!Office_Number = office_list!Office_Number
etc
End If

Could I do this in a query instead ?

Any ideas appreciated.

Missy Ed
Looking to exchange ideas and tips on VB and MS Access development as well as office 97 development. Drop me a line: msedbbw@hotmail.com