UtterAccess.com
We have detected that you are using an unsupported web browser. We recommend you to upgrade your web browser to get the most from UtterAccess forums.
Why should and how can I upgrade?
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: 58,360
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,787
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,787
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,787
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,787
From: Bethesda, MD USA




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: 21st October 2014 - 11:46 PM