UtterAccess.com
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: 57,203
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,527
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,527
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,

I 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?

I read 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,527
From: Bethesda, MD USA



DateDiff (Interval, Date 1, Date 2)

Interval 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.

Mike
Go to the top of the page
 
+
RAZMaddaz
post Nov 18 2010, 10:12 PM
Post #10

UtterAccess VIP
Posts: 7,527
From: Bethesda, MD USA



yw.gif

Awesome!!! 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: 23rd July 2014 - 06:45 AM