[AccessD] 10 days before

Stuart, your function is working great. 
Thanks for sharing.

Pedro Janssen


----- Original Message ----- 
From: "Stuart McLachlan" <stuart at lexacorp.com.pg>
To: "Pedro Janssen" <pedro at plex.nl>; <accessd at databaseadvisors.com>
Sent: Tuesday, April 22, 2003 2:38 PM
Subject: Re: [AccessD] 10 days before


> On 22 Apr 2003 at 13:07, Pedro Janssen wrote:
> 
> > Hello Group,
> > 
> > i have two tables
> > Tablea has 1 field with dates (1 date per year)
> > and TableB has 1 date field (1 record per date from all dates between
> > 1950 and 2002) and 10 number fields.
> > 
> > What i need is from all the dates that are present in TableA, all the
> > records from TableB for 10 days back.
> > 
> 
> Here's one way - 
> 
> Assume TableA contains a Date field called AnnualDate
> Assume TableB contains a Date Field called EDate and a bunch of 
> Number fields.
> 
> 1. Create a temporary table (tblTemp) containing just one Date field 
> (AnnualDate)
> 
> 2. Create a function to populate tblTemp with the desired dates:
> 
> Function MakeDates()
> Dim rs As DAO.Recordset
> Dim rs2 As DAO.Recordset
> DoCmd.RunSQL "Delete * from tblTemp"
> Set rs = CurrentDb.openrecordset("TableA")
> Set rs2 = CurrentDb.openrecordset("tblTemp")
> While Not rs.EOF
>   For loopcount = 0 To 9 
>     rs2.AddNew
>     rs2!AnnualDate = rs!AnnualDate - loopcount
>     rs2.Update
>   Next
>   rs.MoveNext
> Wend
> End Function
> 
> Call this function then:
> 
> 2.  Use a query:
> SELECT TableB.*
> FROM tblTemp INNER JOIN TableB ON tblTemp.AnnualDate = TableB.EDate;
> 
> 
> -- 
> Lexacorp Ltd
> http://www.lexacorp.com.pg
> Information Technology Consultancy, Software Development,System Support.
> 
> 
> 
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>