Solved

Criteria for Date Query

Posted on 2008-06-10
7
4,091 Views
Last Modified: 2013-11-28
My database is a tracking database for a manufacturing facility. I need a query to return data based on dates. For example: when an employee completes a task for a job my database records the date and time of completion. If this task is not completed in an "X" number of days I need to know this. Here's a short list of steps. Order Entry, Saw, Machining, Prep.
For example: The employees have 3 days to Saw the job after Order Entry. What criteria can I use in a query that will show me that my employees have exceeded the allotted 3-day time period?
I hope this is clear.
0
Comment
Question by:ckarrow2
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 12

Expert Comment

by:koutny
Comment Utility
Use the DateDiff function. For example

select * FROM TasksTable WHERE DateDiff("d",[OrderDate],Date) > 3

0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility

select * from tableX
where dateCompletion is null and DateAdd("d",3,[Orderdate])<Date()
0
 
LVL 12

Expert Comment

by:koutny
Comment Utility
Yes, you are right, I forgot to add the bit which checks whether it has been completed or not
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:ckarrow2
Comment Utility
Here's some additional information that may be helpful.
There is one table "Main Table".
The fields used are "Date_Order_Entered" &  "Date_Sawed"
What I need is a query that returns a list of records that have not been sawed three days (or more) after Date_Order_Entered. This would mean no data has been entered at all into Date_Sawed.
I'm sorry to be ignorant, but I'm kinda new to Access. Exact instructions on how to enter the criteria would be helpful. (That's why I've given my actual field names) :)
Thanks for everyone's help!!
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 250 total points
Comment Utility
open a query do not select anty table, select SQL view
copy and paste the codes below

select * from [Main Table]
where [Date_Sawed] is null and DateAdd("d",3,[Date_Order_Entered])<Date()
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 250 total points
Comment Utility

think what you are saying is more like:

select * from [Main Table]
where ([Date_Sawed] is null and DateAdd("d",3,[Date_Order_Entered])<Date()) or (DateDiff("d",[OrderDate],[Date_Sawed]) > 3)
0
 

Author Closing Comment

by:ckarrow2
Comment Utility
Both answers pointed me in the right direction. In a simple query I put Date_Sawed as "Is Null" and "Now() >Date_Sawed+3"
Worked like a charm
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Join & Write a Comment

SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

728 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now