Count values within overlapping date criteria

[lawtonl]

Hello,

I have a crosstab query that selects and counts all contracts that are due within 3 months of Febuary, March and April. I'm trying to pull counts together and I'm using Select Case for the criteria as below:

Public Function Months(DiscDate As Date) As String
 
Select Case DiscDate
 
Case #1/2/2010# To #4/30/2010#: Months = "Feb"
Case #1/3/2010# To #5/31/2010#: Months = "Mar"
Case #1/4/2010# To #6/30/2010#: Months = "Apr"
Case Is < #1/2/2010#: Months = "Out of Contract"
Case Else
Debug.Print DiscDate
 
 
End Select
End Function

If a date is 15/04/10(dd/mm/yyy) for example I would like for that to show up in all 3 counts.

The thing is that it seems to only count febuarys data in full and incorrect values come up for the other 2 months. I assume it because VBA will priotise the count on the first line of the select case?

Is there anyway round this?

tia

 

[DCrake]

A case statement will react to the first matching statement, and that one only. What you are trying to do is not achievable using this method.

Anyway how can April dates be in Feb and Mar?

 

[lawtonl]

When a contract is due for expiry we aim to contact our customers within 3 months prior to try to renew the contract. We have quarterly campaigns to do this but the marketing comes out of one budget. So for the month of feb we get all exipry dates up until the end of april and for march we get all the exiry dates up until the end of may...etc etc.


If that makes any sense...

 

[DCrake]

So really what you are asking for is to filter records whose expiry date is within 90 days of today? or the first day of the campaign? Is this correct?

David

 

[lawtonl]

I've filtered the records now I want to filter counts based on the contact expiry date, so we can monitor the success of the campaign monthly as well as administrative and logistical reasons, like this:

Month 1 - Feb: Count all contracts after 01/02/10 and expire before 30/04/10
Month 2 - Mar: Count all contracts after 01/03/10 and expire before 31/05/10
Month 3 - Apr: Count all contracts after 01/04/10 and expire before 30/06/10

so some of the values from month 1 could also be in month 2 or month 3

This is also crosstabbed with the types of contracts they are on to see upsell opportunities like this, only the values aren't correct:

Contract Type	Feb	Mar	Apr	Out of Contract

1			2	1	2
2		7	14	2	17
3		5	5	8	
4		106	213	130	286

I could do this manually quite simply but I thought maybe, as I'll be doing this quite reguarly there would be a more automated way of achieving this.