Solved

Query items due within the next 5 days

Posted on 2014-02-17
4
496 Views
Last Modified: 2014-02-17
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
0
Comment
Question by:ferguson_jerald
4 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39865657
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
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 39865689
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
 
LVL 10

Expert Comment

by:Banthor
ID: 39865772
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

0
 

Author Closing Comment

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

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

791 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question