subtract dates

  • Thread starter Thread starter a
  • Start date Start date
A

a

table 1
ID BeginDate EndDate
1 1/1/2004 5/1/2004
2 10/1/2004 20/1/2004
3 10/2/2004 20/2/2004
table 2
contain dates take from dates in table1 ((subtracted) (cuts)
ID BeginDate EndDate
1 1/1/2004 3/1/2004
2 10/1/2004 12/1/2004
2 16/1/2004 18/1/2004
3 17/2/2004 20/2/2004
the question is how can I retuern query like the following
Here Is the query for what I want please (see it)
ID BeginDate EndDate
1 4/1/2004 5/1/2004
2 13/1/2004 15/1/2004
2 19/1/2004 20/1/2004
3 10/2/2004 16/2/2004
pleas see that the query return dates not taked in table 2
thank you and hope you can help
 
table 1
ID BeginDate EndDate
1 1/1/2004 5/1/2004
2 10/1/2004 20/1/2004
3 10/2/2004 20/2/2004
table 2
contain dates take from dates in table1 ((subtracted) (cuts)
ID BeginDate EndDate
1 1/1/2004 3/1/2004
2 10/1/2004 12/1/2004
2 16/1/2004 18/1/2004
3 17/2/2004 20/2/2004
the question is how can I retuern query like the following
Here Is the query for what I want please (see it)
ID BeginDate EndDate
1 4/1/2004 5/1/2004
2 13/1/2004 15/1/2004
2 19/1/2004 20/1/2004
3 10/2/2004 16/2/2004
pleas see that the query return dates not taked in table 2
thank you and hope you can help
Click to expand...

a, very sorry not to have gotten back to you sooner.

You will need another table, CalendarTable perhaps. Fill it with every
date you will ever want to consider - for example from today through
2025. You can fill this table using Excel; create a spreadsheet, use
the fill-down feature to create the dates, and copy and paste the
column into CalendarTable.

Create a Query using your table with BeginDate and EndDate, and the
Calendar table; do *not* use any JOIN line. Instead put a criterion on
the CalendarTable date field of

NOT BETWEEN [BeginDate] AND [EndDate]

John W. Vinson[MVP]
(no longer chatting for now)
 
Back
Top