Solved

# Query items due within the next 5 days

Posted on 2014-02-17
498 Views
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
Question by:ferguson_jerald
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 120

Expert Comment

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

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

ID: 39865772
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 Closing Comment

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

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
###### Suggested Courses
Course of the Month6 days, 4 hours left to enroll