Solved

Criteria for Date Query

Posted on 2008-06-10
7
4,093 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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 250 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 250 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

820 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