joining 2 queries from 1 table

[FireViking]

hi all,
There maybe a simple solution I just cant see.
I have a table lets say 'Invoices'. There is an 'invoice paid' date feild and an 'Passed' date feild to record the date the invoice was passed to a debt recovery service.
I have an financial activity report that needs to show all invoices paid and all invoices that were passed over for recovery within a specified period.
It is simple enough to create a query to show all records within the 'Invoice' table. I use the BETWEEN [Start Date] AND [End Date] as a criteria on the invoice paid date feild to return those records paid. However I also need to show the invoices 'Passed' for recovery.
I created 2 queries each showing the relevant records. I then tried to join them but this does'nt return all records. I thought of creating a table and using the query update and have the report read from there. Since I hav'nt tried that yet I thought I might try and get advice first.

Cheers.

 

[LittleSmudge]

Why do you want to JOIN then?
How are you Joining them
If a join is necessary I think you'll need to UNION them

Alternatively
If you want a recordset that contains both sets of data then use

"SELECT ........... WHERE ((PaidDate BETWEEN #" & StartDate & "# AND #" & EndDate & "#) OR (PassedDate BETWEEN #" & StartDate & "# AND #" & EndDate & "#))"

That will give you both lots in one hit.


G LS

 

[FireViking]

Thank you my freind,
I knew there was an easy solution. I could'nt see the trees for the forest.

cheers