X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

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

UtterAccess Member
Posts: 45



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: 59,405
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: 7,952
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

UtterAccess Member
Posts: 45



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: 7,952
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

UtterAccess Member
Posts: 45



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

UtterAccess Member
Posts: 45



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: 7,952
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

UtterAccess Member
Posts: 45



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: 7,952
From: Bethesda, MD USA



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

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 20th December 2014 - 02:25 PM