calculate difference between 2 fields from 2 tables

[gdanalakshmi]

I have 2 tables with 2 columns.

TableA (MoveID, DateA) has all the records.
TableB (MoveID, DateB) has few records. I need to calculate the difference in dates (DateB - DateA). How do I find the differnce when there is no correspoding record in TableB?

 

[ColinEssex]

Use the "find unmatched query wizard"

Col

 

[gdanalakshmi]

I cannot use the unmatched query -

If I use the query, I get the records from TableA that excludes the records in TableB.


I am getting #Error when I try to find a difference in the dates for which there is not correspoding record in TableB.

 

[Rich]

Unless I'm missing something here, if there are no corresponding date records in table B you cannot get a DateDiff, there is no Date for access to work with, which means you'll have to supply a valid date via an Iif statement.

 

[gdanalakshmi]

The time taken by query to run becomes a lot slower when I use IIf.

That is why I was looking for a way to reduce the computation time.

 

[pdx_man]

You must supply a valid date if it is missing from the other table. You can use the Nz function to do this.

DateDiff('d',Nz([DateB], #01/01/02#),[DateA])

Where there is no date, it will use Jan 1st, 2002. Use whatever date you want, or Date() for today's date.