Query based on day range without year (1 Viewer)

[MrHans]

Hello all,

I have a customers table, that includes a "Contract begin date".
I want to calculate how many contracts have been renewed within the last 45 days.

So today is the 27th of July. I want to return all customers that have a contract begin date between 12-06 and 27-07 regardless of the year.
It should, for example, include customers with begin date 13-06-2005 and begin date 26-07-2014.

Additonally, it would be nice if we can exclude the ones that were started this year, since I only need the renewed ones.

I already tried to apply this criteria:

Between Format(DateAdd("d";-45;Date());"dd-mm") and Format(Date();"dd-mm")

Can anyone help me out with this?

 

[Ranman256]

where
(month([contract begin date]) = 6
and
Day([contract begin date]) between 12 and 30)
or
(month([contract begin date]) = 7
and
Day([contract begin date]) between 1 and 27)

 

[MrHans]

Thanks for the quick reponse Ranman, but I need it to calculate it based on the past 45 days.
So when I run this query next month, it should calculate it with 45 days from then...

 

[plog]

Good one. I like the ones that are simply stated but very complex. This is going to require a function in VBA. I don't know how good you are at VBA so I will give you the broad strokes and you can reply if you need more help.

The first 13 days or so in January make this a complex problem. For those 13 days, to implement your criteria, you are going to have to test a range that includes 3 different months (Nov, December and Jan) and 2 different years. So, as you write your function you must keep those dates in the back of your mind to make sure you are catching everything.

You would pass the function a date and it would return a True/False to let you know if the date you passed should be included in the report. The function would run a series of tests to see if it fell within a date within the last 45 days of a year prior to the current one. It would look something like this:

Public Function IsReportDate(in_Date) As Boolean
    ' determines if date is one that should be included in report

ret=True
    ' return value, by default will be true



' logic here to determine if in_Date is a valid date


IsReportDate = ret 
End Function

Then in a query you would make a calculated field like so:

IncludeData: IsReportDate([contract begin date])

And put a 'True' in the criteria area.

The logic of the function is going to include a lot of date functions (http://www.techonthenet.com/access/functions/): Month(), Year(), DateDiff(), Date(). Give it a shot and then post back what you have.

 

[MrHans]

Thanks for the help Plog.
I think I actually found a very simple solution.
I created a second query, based on the first one.

Query 1:
Day: Format([fldSubscriptionStart];"mm-dd")
(formatted by month-day to filter correctly)

Year: Format([fldSubscriptionStart];"yyyy") with Criteria: <>Year(Date())
(this prevents newly activated contracts to be included)

Query 2:
Day: Between Format(DateAdd("d";-45;Date());"mm-dd") And Format(Date();"mm-dd")
(this filters correctly between today and 45 days ago.

I'm not sure how it will work during new year, but I guess I'll find out at that time. For now, this is ok for me.

Thanks again for your input!