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