Solved

Query items due within the next 5 days

Posted on 2014-02-17
4
498 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
[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
  • Learn & ask questions
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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

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…

734 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