• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4100
  • Last Modified:

Criteria for Date Query

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
ckarrow2
Asked:
ckarrow2
  • 2
  • 2
  • 2
  • +1
2 Solutions
 
koutnyCommented:
Use the DateDiff function. For example

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

0
 
Rey Obrero (Capricorn1)Commented:

select * from tableX
where dateCompletion is null and DateAdd("d",3,[Orderdate])<Date()
0
 
koutnyCommented:
Yes, you are right, I forgot to add the bit which checks whether it has been completed or not
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
ckarrow2Author Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
Mark WillsTopic AdvisorCommented:

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
 
ckarrow2Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now