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.



Either the transfers table or orders table has a date in it so my query shows the date on each column for each table, there's blank fields where the other table has the date, so every row only has one date, one field is always blank.

Now I want a 3rd date field that will combine both those dates so every row has a date in it now, make sense.

Any ideas, I've been searching but can't find anything.

[Rod]

I would use the Iif function to test whether the first date is null. If it is use the second date otherwise use the first date. (I assume that the dates are in a date/time field and that by 'blank' you mean null.)

Here's an example SQL:

SELECT Orders.ID, Orders.OrderDamage, Transfers.TransferDamage, IIf(IsNull([OrderDate]),[TransferDate],[OrderDate]) AS MyDate
FROM Orders INNER JOIN Transfers ON Orders.ID = Transfers.ID;

or if you are using the design grid:

Attachment 3790

[robsworld78]

Hi thanks for the great picture however this doesn't work, I'm guessing because the orderID and transferID are joined.

They are always different so it will never display anything.

When I do damages for vendors I do an order

When I do damages for transfers I do a transfer

Both are connected to my inventory table which has a field barsdamaged, barssold, barsgiven, etc...

Now I'm trying to bring all the damages together so I have a report that says overall damages.

I can do that no problem its the date issue. Because there different tables and have there own date I need both date fields in my query but the problem is which one do I pull for the report. If I use the order date then if the product was a cooler transfer no date shows because that date isn't stored in the order date field its stored in the transfer date field in the transfers table.

So if I can get both dates in one column and call it maindate then I can use that on my report and every line will have a date.

[Rod]

Sorry, my publication was an example, you have to adapt it to your own situation. Your 'Orders' and 'Transfers' do need to be joined, either directly or through one or more intermediate tables/queries.

Busy at the moment. Get back to you asap.

[Rod]

Gotcha! My understanding of what you are doing is merging all order and transfer damages bar by bar. Your orders and transfers tables have to have a foreign key indicating the bar concerned. The join is bar-to-bar, either through your inventory table or directly. Your requirement is to have only one resultant date column (surely this applies to other columns as well: damage description?).

My Iif function example applies equally to this scenario.

If you want copy-and-paste code then you will have to publish your query and table design(s).

[robsworld78]

Yeah you understand, everything works great right now if I can get those dates together. I think I was getting closer with your example but needed to move on to other stuff.

I'll play some more and see what I can do, this extra info helps. I won't get back to that for a bit though, 10 other things popped up I need to do. I'll let you know how I do.