03-01-2011 11:10 AM
I’m trying to write a query that basically combines to different queries. Each works well separately but when I try to combine them it doesn’t work. I’m trying to look up all Opportunities that were opened in January that are at stages 7 through 9 “plus” all the Opportunities that were Closed-Won in January regardless of when they were opened.
I know I need to put parenthesis in the right place. I put a space between the two seperate queries and the "Or" is where I'm trying to connect them. I put the last criteria in because
Any Ideas are greatly appreciated
(Opportunity Status Equal to (=) 1 And
Opportunity Actual Close Date On or After 1/1/2011 And
Opportunity Actual Close Date On or Before 1/31/2011 ) Or
Opportunity Open Date On or After 1/1/2011 And
Opportunity Open Date On or Before 1/31/2011 And
(Opportunity Stage Starts With 7 Or
Opportunity Stage Starts With 8 Or
Opportunity Stage Starts With 9 ) End
03-01-2011 12:40 PM
Hello jbailey ....
I think if you make the following changes it will work:
1. change the "or" to "and" at the end of the 3rd line, that will combine the two sets.
2. Add double (( before line 4
3. Add single ) after line 5
4. Add single ( before line 6
5. Add double )) after line 8
I didn't test it, but I think that will group what you want.
03-01-2011 01:30 PM
Your solution was not quite correct but it lead me to the correct solution. Your placement of parenthesis did the trick. If I change the 'Or' on line 3 to 'And', records in the lookup have to meet the criteria in both groups to be included (so when I tried it there were only a few records in the lookup). This was my first impression also.
When I put parenthesis where you suggested it worked great!
I know parenthesis tell the query what to do first. Does that mean "((" are done before "("?
In your example, did the query search for all records where stage start with 7, 8 or 9 and then filter by date range or the other way around?
03-01-2011 02:15 PM
Regarding parenthesis, it is my understanding that the inner most parenthesis are completed before the outer and follows boolean logic.
Hope that helps.