Joining 2 fields in query to make one

[robsworld78]

I have a query made up that brings up damages, I have 2 tables that cover damages, "Transfers" and "Orders" My query has the data I want except I need to combine 2 date fields. On thing to note is both those tables share a table called "inventory" where all the numbers are actually stored. It has an orderID and transferID field on there to keep everything seperate. I'm only interested in the date in "transfers" and "orders"

Right now the query has a field for the transferdate and for the orderdate, only on of these fields will have a date in every row, its either or. A row will never have 2 dates in it because it was either an order that did the damages or a transfer. But now the problem is I want a report with a date on it beside each line but I don't want both fields on my report and have all these gaps I want to join both date fields together. Just imagine sliding those 2 columns together. Then on my report I can call the column that brings them together.

Any ideas, I've been searching hard and trying lots but can't find anything.

 

[jzwp22]

You could use nested functions in your query to create a calculated date field:

SELECT IIF(IsNull(orderdate),transferdate,orderdate) as YourNewDateField, ...
FROM tablename