Solved

Please modify the query to get result i need

Posted on 2008-06-26
10
236 Views
Last Modified: 2010-03-20
Good afternoom!
I have a query showing the results based on date parameters.
So far it works partually:
I want  ------>
if I select the start and end date it should give the data in the range i have specified
if i did not select the start and end date it should give everything
if i select start date only or  end date only it should give the result i want
SELECT CustomerName, Note, BadWO.RecDate
FROM Export INNER JOIN BadWO ON Export.WorkOrderNum = BadWO.WO
WHERE
 (BadWO.RecDate) Between [Enter the Start Date] And [Enter the End Date])
AND BadWO.Reason="Customer not available"
OR
[Enter the Start Date] Is Null  AND (([Enter the End Date]) Is Null));
0
Comment
Question by:rfedorov
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 1

Expert Comment

by:RonJDouglas
ID: 21877678
Use isnull

Between ISNULL(startDate, '01/01/1901') AND ISNULL(endDate,'12/31/2099')
0
 

Author Comment

by:rfedorov
ID: 21878253
Thank a lot for such fast respond, could modify my query directly>>>
0
 
LVL 1

Assisted Solution

by:RonJDouglas
RonJDouglas earned 50 total points
ID: 21878464
I'm obviously not able to test it but something like....
SELECT CustomerName, Note, BadWO.RecDate
FROM Export INNER JOIN BadWO ON Export.WorkOrderNum = BadWO.WO
WHERE
 (BadWO.RecDate) Between ISNULL(startDate, '01/01/1901') AND ISNULL(endDate,'12/31/2099'))
AND BadWO.Reason="Customer not available";

This way if either startDate or endDate is null then it uses the default values.  So if all you pass is the startDate it will use that value and because endDate is null it will use 12/31/2099 giving you everything between your entered start date and 12/31/2099.  You might need to make sure that your startDate and endDate are nulls not ' '
0
 

Author Comment

by:rfedorov
ID: 21878531
i am sorry, but there is no startdate or enddate, there is only RecDate
It is not working, saying wrong number of arguments...
0
 

Author Comment

by:rfedorov
ID: 21878587
let me clarify
SELECT CustomerName, BadWO.WO, BadWO.RecDate
FROM Export INNER JOIN BadWO ON Export.WorkOrderNum = BadWO.WO
WHERE (((BadWO.RecDate) Between [Enter the Start Date] And [Enter the End Date]) AND ((BadWO.Reason)="Customer not available"));

working fine :
in case if you set the parameters the query returns what i want accordingly to parameters

I want to add ability to skip the parameters and return * regardless
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.

 
LVL 1

Expert Comment

by:RonJDouglas
ID: 21878618
I see you are doing this from Access, I have never actually tried this but it should work.... Try....

SELECT CustomerName, Note, BadWO.RecDate
FROM Export INNER JOIN BadWO ON Export.WorkOrderNum = BadWO.WO
WHERE
 (BadWO.RecDate) Between ISNULL([Enter the Start Date], '01/01/1901') AND ISNULL([enter the End Date],'12/31/2099'))
AND BadWO.Reason="Customer not available";
0
 

Author Comment

by:rfedorov
ID: 21878674
same error
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 200 total points
ID: 21878827
SELECT CustomerName, Note, BadWO.RecDate
FROM Export INNER JOIN BadWO ON Export.WorkOrderNum = BadWO.WO
WHERE BadWO.RecDate Between Nz([start date], #1 Jan 1900#) And Nz([end date], #31 Dec 2099#)
0
 
LVL 30

Expert Comment

by:hnasr
ID: 21879066
Table:stRateenDate
ID      dt
1      11/08/2008
2      21/09/2008
3      24/07/2008
4      23/10/2008
5      17/07/2008
6      12/07/2008

Query:
SELECT stDateenDate.ID, stDateenDate.dt
FROM stDateenDate, (SELECT Min([dt]) AS MinDate, Max([dt]) AS MaxDate
FROM stDateenDate)  AS MinMax
WHERE (((stDateenDate.dt) Between nz([Enter Start Date],[MinMax].[MinDate]) And nz([Enter End Date],[MinMax].[MaxDate])));
0
 
LVL 30

Expert Comment

by:hnasr
ID: 21879090
No problem, delayed posting!
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

863 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

19 Experts available now in Live!

Get 1:1 Help Now