Query Help

[renenger]

I am not sure if this is possible, so if anyone could help I would really appreciate it. I have a table tblLotInfo. There is a LotDelDate(Delivery Date) and ReDelDate (Redelivery Date). There is a history table tblHistory where the ShipDate, ReturnDate, and ReShipDate are located.

In my query, I am prompting for a date range for LotDelDate. Well, we are now tracking when units ship, if they are returned and when they are reshipped. We are losing historical data by changing the LotDelDate to input a Redelivery date if the units come back. So I have added a new field to add a redelivery date should this happen.

I have a report that shows the company when units are scheduled to deliver. I need to add units that are scheduled for redelivery as well, disregarding the LotDelDate on these units.

In the query I need to say, If [ReturnDate] is null then pull all lots with LotDelDate between the range. If [ReturnDate] is not null then pull all lots with ReDelDate between the same range. They need to show on the report in Delivery Date range (either LotDelDate or ReDelDate).

Can someone help me with this? Not sure how to query two date fields within the same query. I am guessing I need an IIf statement or some kind of SQL statement to do this.

Thanks.

 

[FoFa]

Write two queries each pulling the same datatype fields in the same order, then union them together. Basically:
Select Col1, Col2, Col3
from MyTable
Where If [ReturnDate] is null then pull all lots with LotDelDate between the range
UNION
Select Col1, Col2, Col3
from MyTable
WHERE If [ReturnDate] is not null then pull all lots with ReDelDate between the same range