• 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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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