• Status: Solved
• Priority: Medium
• Security: Public
• Views: 510

# Query items due within the next 5 days

Hello Experts.

I need the following query to also identify all records where the calculated field [fiveDayWarningIrDue] is between now() and (now()+5).  For example, if today is 2/17/2014, I need to the query to also return all of the records where [fiveDayWarningIrDue] is between 2/17/2014 and 2/22/2014.

Here's what I have so far:
``````SELECT fraudTbl.fraudID, infoRqstTbl.irDt, infoRqstTbl.irDueDt, infoRqstTbl.irDelDt, DateAdd("d",-5,[irDueDt]) AS fiveDayWarningIrDue
FROM fraudTbl INNER JOIN infoRqstTbl ON fraudTbl.fraudID = infoRqstTbl.fraudID
WHERE (infoRqstTbl.irDueDt<NOW() AND isnull(infoRqstTbl.irDelDt))
GROUP BY fraudTbl.fraudID, infoRqstTbl.irDt, infoRqstTbl.irDueDt, infoRqstTbl.irDelDt
ORDER BY infoRqstTbl.irDt;
``````

Any help is greatly appreciated.

Thanks,
J
0
ferguson_jerald
1 Solution

Commented:
change

WHERE (infoRqstTbl.irDueDt<NOW() AND isnull(infoRqstTbl.irDelDt))

with

WHERE (infoRqstTbl.irDueDt Between NOW() And DateAdd("d",5, Now()) AND isnull(infoRqstTbl.irDelDt))
0

Commented:
Are you certain you want NOW() and Now() + 5 and not Date() and Date() + 5?

There is a big difference.  If you data is stored with the irDueDt as a simple data mm/dd/yy, then

WHERE [irDueDt] Between Now() and DateAdd("d", 5, Now())

will not return records that were due today (2/17/14) but will actually select records which are due on the 22nd.  Where as:

WHERE [irDueDt] Between Date() And DateAdd("d", 5, Date()) will return records including 2/17/14 and 2/22/14.

Personally, I perfer:

WHERE [irDueDt] >= Date() AND [irDueDt] < DateAdd("d", 6, Date())

Which will capture all of the records where [irDueDate] is on 17, 18, 19, 20, 21, 22, regardless of whether they have a time value embedded in the due date.
0

Commented:
I am not sure why this is not simper
``````WHERE isnull(infoRqstTbl.irDelDt,now()) < dateadd(day,5,now())
``````
If we are always concerned with values relative to the current date.

If you are looking for 2 distinct windows of time, you can use the 1=0  option

``````SELECT * FROM STUFF
WHERE (1=1) --Some absolute conditions
AND (1=0
OR (value between v1 and v2 )
OR (value between v3 and v4)
)
``````
0

Author Commented:
Thanks fyed!  I added WHERE [irDueDt] >= Date() AND [irDueDt] < DateAdd("d", 6, Date()) to the existing WHERE clause and it worked as needed.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.