Query matching Different Dates??

[Guest]

I have 2 tables with date fields, table1 is titled Date and table2 is titled
DocDate.
I need to extract data where:

[table1].[Date] is one day prior than [table2].[DocDate]

In other words, let's say table 2 date of 07/10/2006 must look at table
one's records with a date of 07/09/2006

There are many other fields in my query, but the results need to be built
around this criteria.

 

[Douglas J. Steele]

Join the two tables the way you would if you were looking for the rows where
the dates actually matched. Once you've completed your query, go into the
SQL of the query (select SQL View from the View menu) and change the line:

ON [table1].[Date] = [table2].[DocDate]

to

ON [table1].[Date] = ([table2].[DocDate] - 1)

If you have problems, post back the actual SQL for your query.

 

[Guest]

That worked great!!

Thanks!

Join the two tables the way you would if you were looking for the rows where
the dates actually matched. Once you've completed your query, go into the
SQL of the query (select SQL View from the View menu) and change the line:

ON [table1].[Date] = [table2].[DocDate]

to

ON [table1].[Date] = ([table2].[DocDate] - 1)

If you have problems, post back the actual SQL for your query.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have 2 tables with date fields, table1 is titled Date and table2 is
titled
DocDate.
I need to extract data where:

[table1].[Date] is one day prior than [table2].[DocDate]

In other words, let's say table 2 date of 07/10/2006 must look at table
one's records with a date of 07/09/2006

There are many other fields in my query, but the results need to be built
around this criteria.