Date Selection

[Leo]

I asked this question before, and didn't get an answer so here it is again
simplified

If I have three fields

code indetifier date
H67 P 03/02/2005
H67 Q 03/02/2005
H67 P 01/02/2005
H67 Q 01/02/2005
H67 P 11/02/2004
H67 Q 11/02/2004



The records are sorted by date (mm/dd/yyyy)

If you notice there is two of the same codes but different identifiers for
each month

However I only want to return these records based on a specific criteris. So
say if this criteria says 2 then I only want the last two months from the
query like below:

H67 P 03/02/2005
H67 Q 03/02/2005
H67 P 01/02/2005
H67 Q 01/02/2005

so only records with dates 03/02/2005 and 01/02/2005

if the criteria says 3, then all the above records would be returned


Does anyone have any idea how I could do that using VBA?


Thanks a lot

Leo

 

[Tim Ferguson]

However I only want to return these records based on a specific
criteris. So say if this criteria says 2 then I only want the last two
months from the query like below:

if the criteria says 3, then all the above records would be returned


Does anyone have any idea how I could do that using VBA?

Create a querydef:

PARAMETERS StartDate DATETIME;
SELECT IDNumber, FunnyLetter, StoredDate
WHERE StoredDate > StartDate
ORDER BY IDNumber, FunnyLetter

Then call it using

' MonthsToCheck = 3
Set qdf = QueryDefs("MyNewQueryDef")
qdf.Parameters("StartDate") = _
DateAdd("m", -1 * MonthsToCheck, Date())
Set rst = qdf.OpenRecordset(dbOpenSnapshot, dbForwardOnly)


You could also build the SQL in inline code if you preferred, but you get
the picture.


Hope that helps


Tim F

 

[John Spencer (MVP)]

So if you enter 1, you want just March; enter 2 and you want March and January;
Enter 3 you want November, January, and March, etc.

You can't do this with a parameter query, but you could build your query in VBA

Dim strSQL as String, dbAny as DAO.Database, rstAny as DAO.Recordset
Dim FirstDate as Date, LastDate as Date

StrSQL = "SELECT TOP " & intMonths & _
" Format([Date],""yyyy/mm/"") as Dates " & _
" FROM TheTable " & _
" GROUP By Format([Date]), ""yyyy/mm/"") " & _
" ORDER BY Format([Date]), ""yyyy/mm/"") Desc "

' Use that to open a recordset
Set rstAny = dbAny.OpenRecordset (strSQL)

FirstDate = CDate(rstany.Fields(0) & "01")
rstAny.MoveLast
LastDate = CDate(rstany.Fields(0) & "01")
LastDate = DateSerial(Year(LastDate),Month(LastDate)+1,0)

'Now you can use the two dates to build your query.
'-- Set controls on a form equal to the values or
'-- Build the sql statement directly in VBA or
'-- ?? use some other method to get the values into your query.