Solved

Please modify the query to get result i need

Posted on 2008-06-26
10
253 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Familiarize people with the process of utilizing SQL Server views 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 Access…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

685 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