Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Criteria for Date Query

Posted on 2008-06-10
7
Medium Priority
?
4,098 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
ID: 21750385
Use the DateDiff function. For example

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

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 21750418

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

Expert Comment

by:koutny
ID: 21750657
Yes, you are right, I forgot to add the bit which checks whether it has been completed or not
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

Author Comment

by:ckarrow2
ID: 21750917
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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 1000 total points
ID: 21750945
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 1000 total points
ID: 21753680

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
ID: 31465694
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

916 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