?
Solved

Please modify the query to get result i need

Posted on 2008-06-26
10
Medium Priority
?
267 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 200 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
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)

 

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 93

Accepted Solution

by:
Patrick Matthews earned 800 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

765 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