asked on

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;

Open in new window

Any help is greatly appreciated.

Thanks,
J

Rey Obrero (Capricorn1)

change

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

with

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

ASKER CERTIFIED SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Banthor

I am not sure why this is not simper

WHERE isnull(infoRqstTbl.irDelDt,now()) < dateadd(day,5,now())

Open in new window

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)
)

Open in new window

ferguson_jerald

ASKER

Thanks fyed! I added WHERE [irDueDt] >= Date() AND [irDueDt] < DateAdd("d", 6, Date()) to the existing WHERE clause and it worked as needed.