Query matching Different Dates??

L

Lucien

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.
 
D

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.
 
L

Lucien

That worked great!!

Thanks!



Douglas J. Steele said:
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)


Lucien said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top