UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

 
   Reply to this topicStart new topic
> query criteria to show item overdue needed    
 
   
lightwriter
post Nov 18 2010, 11:28 AM
Post#1



Posts: 45
Joined: 26-September 10



Hello,
I have a query with a DateIssued and a DateDue field.
Can anyone show me the criteria I need to display items that are overdue?
Thanks,
Mike
Go to the top of the page
 
theDBguy
post Nov 18 2010, 11:44 AM
Post#2


Access Wiki and Forums Moderator
Posts: 61,779
Joined: 19-June 07
From: SoCal, USA


Hi Mike,
Maybe something like?
... WHERE DateDue < Date()
Hope that helps...
Go to the top of the page
 
RAZMaddaz
post Nov 18 2010, 11:46 AM
Post#3


UtterAccess VIP
Posts: 8,144
Joined: 23-May 05
From: Bethesda, MD USA


Mike,
If you add the Fields you want in the Query, then for the Criteria of the DateDue Field, enter <=Date() and this will return all overdue items where the DateDue is Today or before Today. Is this what you wanted?
RAZ
Go to the top of the page
 
lightwriter
post Nov 18 2010, 12:12 PM
Post#4



Posts: 45
Joined: 26-September 10



Hi RAZ,
That works great!
Working from this, I need to calculate the number of days an item is overdue, can you direct me?
Thanks,
Mike
Go to the top of the page
 
RAZMaddaz
post Nov 18 2010, 12:21 PM
Post#5


UtterAccess VIP
Posts: 8,144
Joined: 23-May 05
From: Bethesda, MD USA


Well, in the Query you can add an expression.
NumberofOverdueDays:DateDiff("d", DateDueField, Date() )
Go to the top of the page
 
lightwriter
post Nov 18 2010, 12:22 PM
Post#6



Posts: 45
Joined: 26-September 10



Hi Paul,
That does the job!
Working from this, I need to calculate the number of days an item is overdue, can you direct me?
Thanks,
Mike
Go to the top of the page
 
lightwriter
post Nov 18 2010, 12:49 PM
Post#7



Posts: 45
Joined: 26-September 10



Hi Raz,
hope I am not asking too much here but I am trying to learn from your knowledge.
Would you mind breaking down NumberofOverdueDays:DateDiff("d", DateDueField, Date() ) so that I get an understanding of this expression?
Oread elsewhere that DateDiff returns a Variant (Long) specifying the number of time intervals between two specified dates, but I am not clear as to what the two sets of run-on words represent (are they functional or merely visual cues?), I am not even where in the query to put the expression.
I appreciate your help,
Mike
Go to the top of the page
 
RAZMaddaz
post Nov 18 2010, 01:14 PM
Post#8


UtterAccess VIP
Posts: 8,144
Joined: 23-May 05
From: Bethesda, MD USA


DateDiff (Interval, Date 1, Date 2)
nterval can be Year (YYYY), Quarter (Q), Month (M), Day of Year (Y), Day (D), etc...
Date 1 - is the one of the two dates you want to calculate the difference between. This can entered by using the name of a Field or you enter the date surrounded by the pound sign ( #) like the following: #01/01/2010#
Date 2 - This is the second of the two dates and will be the later of the two dates.
So I did an interval of Days between your Field DateDue and today's Date Date().
Go to the top of the page
 
lightwriter
post Nov 18 2010, 05:42 PM
Post#9



Posts: 45
Joined: 26-September 10



Thank you RAZ, you are very kind as well as knowledgeable.
ike
Go to the top of the page
 
RAZMaddaz
post Nov 18 2010, 10:12 PM
Post#10


UtterAccess VIP
Posts: 8,144
Joined: 23-May 05
From: Bethesda, MD USA


br />wesome!!! Glad I could help you!!!!!!
RAZMaddaz
Go to the top of the page
 


RSSSearch   Top   Lo-Fi    19th April 2015 - 04:22 AM