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

Please modify the query to get result i need

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
rfedorov
Asked:
rfedorov
  • 4
  • 3
  • 2
  • +1
2 Solutions
 
RonJDouglasCommented:
Use isnull

Between ISNULL(startDate, '01/01/1901') AND ISNULL(endDate,'12/31/2099')
0
 
rfedorovAuthor Commented:
Thank a lot for such fast respond, could modify my query directly>>>
0
 
RonJDouglasCommented:
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
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.

 
rfedorovAuthor Commented:
i am sorry, but there is no startdate or enddate, there is only RecDate
It is not working, saying wrong number of arguments...
0
 
rfedorovAuthor Commented:
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
 
RonJDouglasCommented:
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
 
rfedorovAuthor Commented:
same error
0
 
Patrick MatthewsCommented:
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
 
hnasrCommented:
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
 
hnasrCommented:
No problem, delayed posting!
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.

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