Eliminate a record not in date range

[timbits35]

Hi,

I have a query as follows :

SELECT tblCase.CaseId, tblCase.ReqReceived, tblCase.Letter_AMPI
FROM tblCase
WHERE (((tblCase.Letter_AMPI) Between #4/1/2014# And #3/31/2015#)) OR (((tblCase.ReqReceived) Between #4/1/2014# And #3/31/2015#))
ORDER BY tblCase.CaseId;

I am looking for all records where either the field Letter_AMPI or the field ReqReceived falls between the specified date range, EXCEPT as below.

However there are cases where first there is a date for Letter_AMPI and then a few weeks letter, we get a special request for the same case which is the ReqReceived date. So for example we may get a case which has an Letter_AMPI date of 01/01/2014 (not in the date range), but a ReqReceived date of 05/10/2014 (in the date range). So I need to be able to eliminate this record because if the Letter_AMPI does not fall in the date range, it should not appear in the results.

I know that it does not work because of the OR but I need the OR to pick up the other records.

Can anyone help me please?

Thank you.

 

[plog]

I know that it does not work because of the OR but I need the OR to pick up the other records.

The other records logically means that you want to include records with Letter_AMPI inside the date range and ReqReceived outside the date range. Is that correct?

To be clear:

CaseId, Letter_AMPI, ReqReceived
1, 4/2/2014, 5/2/2014
2, 3/2/2014, 5/2/2014
3, 3/12/2015, 4/2/2015
4, 3/31/2014, 5/1/2015

Only records 1 & 3 should make it into the final results of your query? Is that correct?

 

[timbits35]

There are 2 types of cases regular and amplified. And depending on the case, there may be 1 of the 2 dates or both of them.

In regular cases, there is only 1 date, the ReqReceived date. In my query this date has to fall within the date range.

In amplified cases, we always have the date Letter_AMPI at a minimum and we may have only this date. But later on, the case may be also assigned a ReqReceived date, so now the case has 2 dates. However, for the purpose of the query, it is the Letter_AMPI that has to fall within the date range if this particular record has 2 dates. Also, if there is both a Letter_AMPI and ReqReceived date, ReqReceived date will always be at least the same date or later.

CaseId, Letter_AMPI, ReqReceived
1, 4/2/2014, 5/2/2014 - within date range
2, 3/2/2014, 5/2/2014 - outside date range
3, 3/12/2015, 4/2/2015 - within date range
4, 3/31/2014, 5/1/2015 - within date range

5, 03/15/2014, blank - within date range
6, 01/01/2014, blank - outside date range
7, blank, 05/10/2014 - within date range
8, blank, 04/15/2015 - outside date range.

Hope this helps,
Thank you

 

[plog]

Yes that helps. But are you sure about #5?

5, 03/15/2014, blank - within date range

3/15/2014 is outside the parameters. If not that's fine too, my advice will be the same.

You have 8 cases, 5 of which result in inclusion. This can be done using a hugely complex criteria statement, but my advice would be to move this logic to a function and have it do the logic and return True/False based on if the record should be included or not.

Your WHERE statement would be come this:

WHERE IsWithinRange([Letter_AMPI], [ReqReceived]) = True

Then you put a function like this in a module:

Public Function IsWithinRange(in_LetterDate, in_ReceivedDate) AS Boolean
    ' determines if record is within range for inclusion of query

ret = False          ' return value, by default is false


' logic for the 5 True cases below

IsWithinRange = ret

End Function

Additionally, I saw another post where you want to make this a parameter query. Making your WHERE clause less complex will help you in that. You would change your function to accept 2 more dates (the parameters) and then compare in_LetterDate and in_ReceivedDate to those values.

 

[timbits35]

Yes, you are right about #5.

The reason for this post is I do not know how to create the logic that is necessary. So I do not know how to add this logic to the function.

Thank you

 

[plog]

Google some VBA tutorials. To get you started I'll do the first case:

Public Function IsWithinRange(in_LetterDate, in_ReceivedDate, in_YearStart) AS Boolean
    ' determines if record is within range for inclusion of query based on in_YearStart

ret = False                                                              ' return value, by default is false
dt_PeriodStart = in_YearStart                                  ' first date of Period
dt_PeriodEnd = DateAdd("yyyy", 1, in_YearStart)     ' first date of next Period 


If ((in_LetterDate >= dt_PeriodStart) AND (in_LetterDate < dt_PeriodEnd) AND (in_ReceivedDate >= dt_PeriodStart) AND (in_ReceivedDate < dt_PeriodEnd)) Then ret=True
    ' Case 1: Letter Date and Received Date both within time period--return True  

' logic for the other 3 True cases below

IsWithinRange = ret

End Function

 

[timbits35]

This seems awfully complicated. Considering that my current query works fine except for the 1 situation, that I originally described, isn't there any easier way?

Thank you

 

[plog]

Your criteria is complicated--there are 8 cases, at least half you want to include. Your current criteria has false positives and false negatives.

Looking back I question #4 as well:

4, 3/31/2014, 5/1/2015 - within date range

3/31/2014 is before the date range, and 5/1/2015 is after. Are you sure that is to be included?

 

[timbits35]

Maybe this will make it easier.

The date range that I am looking at is a fiscal year. Each fiscal year starts on April 1st and ends on March 31. Maybe I made a mistake with my example records.

All I am trying to do is check to see whether the dates fall in this range. If the record has a date in the field ReqReceived, I check to see if it is in the date range or if it has a field in the Letter_AMPI field I check to see if it is in the date range. The only problem is when I have a date in both fields. In that situation, the date that I have to check to see if it falls within the date range is the Letter_AMPI field.

Hope that helps.

 

[plog]

That makes it a little easier, however it still produces 4 permutations:

Permutation, Letter, Recvd
1, Date, Date
2, Date, No Date
3, No Date, No Date
4, No Date, Date

The test for 1 & 2 can be combined, so that leaves 3 pieces of logic you need to write. I still advise writing this in VBA because those logic statements are going to be pretty long and jamming them into a query is a bad idea (especially if you ever want to use this logic without hitting the query).

Further, I'd create a VBA function that you can pass a date and it returns a fiscal year. Code for that is all over this forum, in fact I saw a post asking about it within the last 3 days.

 

[namliam]

Sounds like fairly straightforward logic to me.... something like:

SELECT tblCase.CaseId, tblCase.ReqReceived, tblCase.Letter_AMPI
FROM tblCase
WHERE  tblCase.Letter_AMPI Between #4/1/2014# And #3/31/2015#
    OR (    tblCase.ReqReceived Between #4/1/2014# And #3/31/2015#
        and tblCase.Letter_AMPI is null ) 
ORDER BY tblCase.CaseId;

Only check ReqRecieved if there is no AMPI date...

 

[plog]

Maybe, you might have missed one case:

Letter_AMPI = 3/31/2014
ReqReceived = 4/2/2014

Never got resolution on that one. Tim, should that record be included or not?

 

[timbits35]

The SQL statement that you provided namliam worked perfectly so Thank you.

 

[namliam]

One more delivery, glad I could help