Query matching Different Dates?? Lucien 20.07.2006 23:05:02 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.

Re: Query matching Different Dates?? "Douglas J. Steele" <NOSPAM_djsteele[ at ]NOSPAM_canada.com> 21.07.2006 00:55:05 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 http://I.Am/DougSteele (no private e-mails, please) "Lucien" <Lucien[ at ]discussions.microsoft.com> wrote in message news:CC4C268D-96B8-426A-B4B0-E3967103634F[ at ]microsoft.com... [Quoted Text] >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.

Re: Query matching Different Dates?? Lucien 21.07.2006 15:32:02 That worked great!! Thanks! "Douglas J. Steele" wrote: [Quoted Text] > 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 > http://I.Am/DougSteele > (no private e-mails, please) > > > "Lucien" <Lucien[ at ]discussions.microsoft.com> wrote in message > news:CC4C268D-96B8-426A-B4B0-E3967103634F[ at ]microsoft.com... > >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. > > >