I have a query which has a date range perameter (between date1 and date2) and column value 1 is -1, or column value 2 is -1, or column value 3 is -1. When I execute the query by dates, the dates are ignored and the column values are referenced. Why is the date range criteria being ignored??
Query Not Working As Expected (1 Viewer)
- Thread starter danbl
- Start date
I guess all of those are possibilities but the expectation is not. Between two dates if either of the three columns has a negative 1 value then the query should show the data. However it pulls all data regardless of the date range that is entered. So it seems that the problem is with the query not the data or the expectation. While I make no claim to be an expert, this is the code from the query.
SELECT [t_Perioperative Data].DOB, [t_Perioperative Data].Date, [t_Perioperative Data].Physician, [t_Perioperative Data].[Peroperative Indicator], [t_Perioperative Data].Q17, [t_Perioperative Data].Q18, [t_Perioperative Data].Q19, [t_Perioperative Data].Comments
FROM [t_Perioperative Data]
WHERE ((([t_Perioperative Data].Date) Between [Forms]![Date Range Form]![Start Date] And [Forms]![Date Range Form]![End Date]) AND (([t_Perioperative Data].Q17)=-1)) OR ((([t_Perioperative Data].Q18)=-1)) OR ((([t_Perioperative Data].Q19)=-1));
If you can help thantks ........
SELECT [t_Perioperative Data].DOB, [t_Perioperative Data].Date, [t_Perioperative Data].Physician, [t_Perioperative Data].[Peroperative Indicator], [t_Perioperative Data].Q17, [t_Perioperative Data].Q18, [t_Perioperative Data].Q19, [t_Perioperative Data].Comments
FROM [t_Perioperative Data]
WHERE ((([t_Perioperative Data].Date) Between [Forms]![Date Range Form]![Start Date] And [Forms]![Date Range Form]![End Date]) AND (([t_Perioperative Data].Q17)=-1)) OR ((([t_Perioperative Data].Q18)=-1)) OR ((([t_Perioperative Data].Q19)=-1));
If you can help thantks ........
Last edited:
- Local time
- Today, 14:41
- Joined
- Feb 19, 2002
- Messages
- 43,257
I removed the excessive parentheses and replaced them with the only ones necessary to accomplish your goal -
I didn't take the time to decode your existing statement but I'm going to guess that the parentheses were incorrectly placed.
Your criteria has two conditions and each is compound. The first looks for a date between two other dates and the second is that one of three things needs to be true. The major conditions are connected with an AND and each of the major conditions is enclosed with in a single paren pair.
It is imperative when using AND, OR, and NOT in a compound condition that you use parehthese to control the order of operation. The default order is NOT, AND, and OR. You want the OR's to be evaluated together and that result AND'd with the other condition which is a date range.
You can read more on order of precedence which impacts both logical and mathematical (*/+-) operations.
Code:
([t_Perioperative Data].Date Between [Forms]![Date Range Form]![Start Date] And [Forms]![Date Range Form]![End Date]) AND ([t_Perioperative Data].Q17 = -1 OR [t_Perioperative Data].Q18 = -1 OR [t_Perioperative Data].Q19 = -1)Your criteria has two conditions and each is compound. The first looks for a date between two other dates and the second is that one of three things needs to be true. The major conditions are connected with an AND and each of the major conditions is enclosed with in a single paren pair.
It is imperative when using AND, OR, and NOT in a compound condition that you use parehthese to control the order of operation. The default order is NOT, AND, and OR. You want the OR's to be evaluated together and that result AND'd with the other condition which is a date range.
You can read more on order of precedence which impacts both logical and mathematical (*/+-) operations.
Thanks for your help Pat ..... after cutting and pasting your code into the query it worked the way I wanted. I went back to design mode to also see what impact in had there. I believe I get the drift now and will read up as you suggested. Thanks again!!
Dan
Dan
