query to filter a date range regardless of year

[tbac]

I need to have a query that filters records on dates to find fruit varieties that ripen in a similar timing as the variety that is on a specific form. The problem is that the ripening times from the reference table have been entered over the years and all have different years. I dont care what the year is, I just want to show varieties that ripen within a few days either way of a specific variety. This is the type of criteria I would use on the query, which will filter a sub-report that will appear in a main report:
>[Forms]![EVALCURRENTSINGLE]![Ripe]-7 And <[Forms]![EVALCURRENTSINGLE]![Ripe]+5

 

[rac2]

Try the DatePart("y", date_ripened) function. This may give the number of days into the year, I believe it is the Julian day. The criterion would be

DatePart("y",date_ripened) BETWEEN ( DatePart("y", ripe) - 7 ) AND ( DatePart("y", ripe) + 5 )

where date_ripened is a column in a table giving the date the fruit ripened in some previous year and ripe is a date entered on the form.

Convert the target date entered on the form to the Julian day, add and subtract a few days. That gives a range of values for the Julian day from the history.

 

[tbac]

Thank you, that worked perfectly. Now I am trying to develop a Report that shows one fruit variety per page, with a SubReport that shows all varieties that ripen within 10 days before, to 10 days after the variety. Should I define the Child-Master link on the report to do that, or can I set up a query that is linked to the ripe time? I already tried the later and the query seems to ignore the filter criteria.

 

[GingerR]

duplicate post

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html