AND/OR Querry Help

[prajithk.15]

Guys,


Could you please help me to make a query with the below conditions from the attached test.mdb file?

1. Enq Received Date 10 days before the search date AND/OR
2. Technical Sub Date 4 weeks in advance of the search date AND/OR
3. Potential Date 4 weeks in advance of the search date

The querry will display all the records which satisfies the above conditions.

Thanks in Advance.

Prajith

 

[burrcm]

Are they really AND/OR remembering that if they are all AND you will get results which only fulfill ALL of the conditions. OR willl give those which fulfill ANY of the conditions. It is not difficult, you need a query for each condition then one which draws its result set from all three. Just clarify the conditions, and I will supply the result.

Chris B

 

[prajithk.15]

Thanks Chris. Yes it is AND/OR. I mean i need records either from the first condition or from the other also.

please advise.
Thanks,
prajith

Are they really AND/OR remembering that if they are all AND you will get results which only fulfill ALL of the conditions. OR willl give those which fulfill ANY of the conditions. It is not difficult, you need a query for each condition then one which draws its result set from all three. Just clarify the conditions, and I will supply the result.

Chris B

 

[burrcm]

OK. I am still not quite convinced that this outcome is what you seek. Note that the outcome if the stated conditions are met will change day by day. I wonder for example if the first condition should be 10 days OR MORE or 10 days OR LESS than the search date rather than exactly 10 days before the search date. Have a look at my queries and let me know.

Regards

Chris B

 

[burrcm]

Not happy with my summary query. Here is another example, but it is still not good. Best bet I think would be to append the results of the first three to a new table, then produce the final result from there.

Regards

Chris B

 

[ajetrumpet]

Could you please help me to make a query with the below conditions from the attached test.mdb file?

1. Enq Received Date 10 days before the search date AND/OR
2. Technical Sub Date 4 weeks in advance of the search date AND/OR
3. Potential Date 4 weeks in advance of the search date

Praj, the queries in your database have JOINS that are not valid, but regardless, what you want to do should not be difficult. The way you write a query to comply with ANY COMBINATION of conditions that are entered on a form (given the fact that there is more than one criteria (in your case, DATES) that CAN be filled in if the user wants to) is like this....

SELECT()
FROM()
WHERE (([table].[enq received date] = DateAdd("d", -10, [search date control]) OR [table].[enq received date] IS NULL) AND
([table].[technical sub date] = DateAdd("ww", 4, [search date control]) OR [table].technical sub date] IS NULL) AND
etc, etc...);

ALSO, This....

WHERE ([table].[enq received date] = DateAdd("d", -10, [search date control]) OR [table].enq received date] IS NULL)

can be simplfied to....

WHERE ([table].[enq received date] = Nz([search date control))

If the user is going to be entering exact dates on which to search than, the above would work. If say, for example, they would enter a "tech sub date" in order to view all the projects with "sub dates" NO LATER than the date they entered, all you have to do is change your operators to "<=" for each criteria.

 

[prajithk.15]

Trumpet,

Thanks for your help. Here in my case there will be one search button on the form and when the user click on that it should show the result as per the criteria. I mean user will not enter any dates.that's the idea!. I have no idea about the 'Search Control' in your querry. Is it the control's name you mean? sorry if i am wrong.Can you make it more clear and simple?

please advise,
thanks,
prajith

Praj, the queries in your database have JOINS that are not valid, but regardless, what you want to do should not be difficult. The way you write a query to comply with ANY COMBINATION of conditions that are entered on a form (given the fact that there is more than one criteria (in your case, DATES) that CAN be filled in if the user wants to) is like this....

SELECT()
FROM()
WHERE (([table].[enq received date] = DateAdd("d", -10, [search date control]) OR [table].[enq received date] IS NULL) AND
([table].[technical sub date] = DateAdd("ww", 4, [search date control]) OR [table].technical sub date] IS NULL) AND
etc, etc...);

ALSO, This....

WHERE ([table].[enq received date] = DateAdd("d", -10, [search date control]) OR [table].enq received date] IS NULL)

can be simplfied to....

WHERE ([table].[enq received date] = Nz([search date control))

If the user is going to be entering exact dates on which to search than, the above would work. If say, for example, they would enter a "tech sub date" in order to view all the projects with "sub dates" NO LATER than the date they entered, all you have to do is change your operators to "<=" for each criteria.

 

[prajithk.15]

Chris,

I have checked the querry you made and i made some changes. I mean instead of giving Enq Received date <Date()-10 we should give the criteria as 'Between Date()-10 AND Date(). Query1,2 and 3 shows the exact rows, but the final Query doesnt.As you said i beleive we shoul append this automatically into a table.But i dont know how to do this automatically, i mean without manually running the each queries to append.

Is there any other ideas without appending to the table?

Thanks for your help.

prajith

Not happy with my summary query. Here is another example, but it is still not good. Best bet I think would be to append the results of the first three to a new table, then produce the final result from there.

Regards

Chris B

 

[ajetrumpet]

Here in my case there will be one search button on the form and when the user click on that it should show the result as per the criteria. I have no idea about the 'Search Control' in your querry. Is it the control's name you mean? sorry if i am wrong.Can you make it more clear and simple?

The SQL I have given you should do it without any problem. SEARCH CONTROL is the NAME of the control in which the criteria lies. So, if you have say 5 combo boxes in which to choose criteria from, you will have 5 DIFFERENT SEARCH CONTROLS in your SQL statment (just follow the pattern in the statement). I included your original question in my last post, you should have no problem with it.

I don't know what you're doing otherwise, but if your criteria will not include any dates (per your statement)....

I mean user will not enter any dates.that's the idea!.

perhaps you should say what EXACTLY will be inputted by the user, if anything (your original question implies the need for a criteria query)....

 

[prajithk.15]

Where will i write this SQL? Confused!
I need to show this result in a report.

please help!
prajith

Trumpet,

Thanks for your help. Here in my case there will be one search button on the form and when the user click on that it should show the result as per the criteria. I mean user will not enter any dates.that's the idea!. I have no idea about the 'Search Control' in your querry. Is it the control's name you mean? sorry if i am wrong.Can you make it more clear and simple?

please advise,
thanks,
prajith

 

[ajetrumpet]

1. Enq Received Date 10 days before the search date AND/OR
2. Technical Sub Date 4 weeks in advance of the search date AND/OR
3. Potential Date 4 weeks in advance of the search date

The querry will display all the records which satisfies the above conditions.

In SQL VIEW - query window...

SELECT()
FROM()

WHERE (([table].[enq received date] <= DateAdd("d", -10, Date()) 
OR ([table].[technical sub date] >= DateAdd("ww", 4, Date()) 
OR ([table].[potential date] >= DateAdd("ww", 4, Date()));

I assume when you say "I need it to satisfy either one or the other conditions", you really mean, "I know that the query will retrieve records where one condition is true, but another one is false." This will happen with the "OR" operator, just be aware.

I also assume that you're not wanting to search for records that ONLY EQUAL the specific date referenced (that is why I added the < > operators with the equal signs). If you did, your query would return an empty recordset.

Other things you might want to try...

Run the report when the search button is clicked

Private Sub SearchButtonName_On_Click

  DoCmd.OpenReport "YourQueryName"

End Sub

**Create an AutoReport based on the Query (Recordset of Report = QueryName)[/b

Here is a JPEG of the results you should get with the SQL I've given you; hope you get it sorted out...

 

[prajithk.15]

ajetrumpet,

It shows me all the records after the current date, but i needed the records between the current date and criteria date. I used between function to get it sorted and it seems to be ok.

You really gave me the idea and it has been a great support from you. I really appreciate your interest.

Thanks a lot.
prajith

In SQL VIEW - query window...

SELECT()
FROM()

WHERE (([table].[enq received date] <= DateAdd("d", -10, Date()) 
OR ([table].[technical sub date] >= DateAdd("ww", 4, Date()) 
OR ([table].[potential date] >= DateAdd("ww", 4, Date()));

I assume when you say "I need it to satisfy either one or the other conditions", you really mean, "I know that the query will retrieve records where one condition is true, but another one is false." This will happen with the "OR" operator, just be aware.

I also assume that you're not wanting to search for records that ONLY EQUAL the specific date referenced (that is why I added the < > operators with the equal signs). If you did, your query would return an empty recordset.

Other things you might want to try...

Run the report when the search button is clicked

Private Sub SearchButtonName_On_Click

  DoCmd.OpenReport "YourQueryName"

End Sub

**Create an AutoReport based on the Query (Recordset of Report = QueryName)[/b

Here is a JPEG of the results you should get with the SQL I've given you; hope you get it sorted out...