Solved

Query items due within the next 5 days

Posted on 2014-02-17
4
491 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 119

Expert Comment

by:Rey Obrero
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now