Solved

Please modify the query to get result i need

Posted on 2008-06-26
10
227 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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

706 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

12 Experts available now in Live!

Get 1:1 Help Now