Find dates from two tables

[tjbennett]

I have two tables, a current orders entered table and a orders entered history table. I want to be able to enter a beginning date and an ending date which would search both tables for orders entered between those dates. I need both tables because if the dates fall in the current month it would need to look in the ORDERS ENTERED table, but, if the dates are previous to the current month it would have to look in the ORDERS ENTERED HISTORY table. I'm not sure how to make a query that allows me to enter the beginning date and ending dates and have MS Access retreive the orders from the entered dates.

 

[PHV]

Have a look at UNION query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Knicks]

Not sure if this angle will work for you but you could try

1. Have an input form with 2 unbound fields for beginning date and ending date

2. Create an unbound report with 2 subreports --- each subreport will look to your tables mentioned above but of course through respective queries. Make each query use the same fields from the input form on the proper date field. It should look something like this in the query criteria

Between [Forms]![frmDate]![Text4] And [Forms]![frmDate]![Text6]

3. From the input form use an OK button to open the unbound report with thee 2 subreports embedded. You can also set the on open event of the unbound form to hide the input form and the on close event to close the input form