Eliminate a record not in date range

timbits35

Registered User.
Local time
Today, 08:06
Joined
Nov 3, 2011
Messages
33
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.
 
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?
 
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
 
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:

Code:
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.
 
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
 
Google some VBA tutorials. To get you started I'll do the first case:

Code:
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
 
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
 
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?
 
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.
 
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.
 
Sounds like fairly straightforward logic to me.... something like:
Code:
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...
 
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?
 
The SQL statement that you provided namliam worked perfectly so Thank you.
 

Users who are viewing this thread

Back
Top Bottom