SQL Code Error?

[Lou_Reed]

In the tutorial shown here: http://www.functionx.com/access/Lesson29.htm

I am at the point where the SQL code should look like this:

SELECT Properties.[Property Type],
Properties.City,
Properties.State,
Properties.[ZIP Code],
Properties.Bedrooms,
Properties.Bathrooms,
Properties.Stories,
Properties.[Year Built],
Properties.Condition,
Properties.[Market Value]
FROM Properties
WHERE (((Properties.[Property Type])="single family") OR
(((Properties.[Property Type])="townhouse")) AND
((Properties.[ZIP Code]) Between 20850 And 20950)) ;

However, it does not produce the output shown in the tutorial. The output has in fact more records with
: 13 as opposed to the previous steps 10. The modifications of the SQL were supposed to eliminate more records leaving
only the MD state with single family and townhouses. That is a total of 4 records. The output looks like this in datasheet view:

Property Type	City	State	ZIP Code	Bedrooms	Bathrooms	Stories	Year Built	Condition	Market Value
Single Family	Silver Spring	MD	20904	4	2.5	3	1995	Good Shape	$495,880.00
Single Family	Alexandria	VA	22231	4	3.5	2	2000	Excellent	$620,724.00
Single Family	Laurel	MD	20707			2		Good Shape	$422,625.00
Single Family	Alexandria		22024	3			1965		$345,660.00
Single Family	Martinsburg	WV	25401	4	3.5	3	2005	Good Shape	$325,000.00
Single Family	Silver Spring	MD	20906	3	3	3	1996	Excellent	$625,450.00
Single Family	Chevy Chase		20956			3	2001		$525,450.00
Single Family	Washington	DC	20008	5	3.5	3	2000	Good Shape	$555,885.00
Single Family	Falls Church	VA		5	2.5	2	1995	Excellent	$485,995.00
Single Family	Washington	DC	20004	4	3.5	1	2004	Good Shape	$735,475.00
Single Family	Gaithersburg	MD	20872	4	2.5	1	1965	Unknown	$615,775.00
Townhouse	Rockville	MD	20854	3	2.5	2	1988	Good Shape	$525,995.00
Single Family	York	PA	17405	4	3.5	3	2002	Excellent	$326,885.00

When there should only be 4 records, those in MD and with a zip code between 20850 and 20920.

I reordered the code the way the tutorial said to do. My output is simply incorrect. So what is wrong with my
code or is it the tutorial?

Thanks in advance.

Respectfully,


Lou Reed

[JoeM]

You parentheses are not correct, so you are essentially checking to see if it is either property type OR that zip code.
Try changing your WHERE like this:

WHERE (((Properties.[Property Type])="single family") OR 
((Properties.[Property Type])="townhouse")) AND
((Properties.[ZIP Code]) Between 20850 And 20950);

This is, of course, assuming that your Zip Code is numeric and not Text. If it is Text, you will need quotes around them.

[Lou_Reed]

I tried it and it worked! Now the question is what was wrong with my original code?

Also does the code shown in the Lesson 29 tutorial work? It is different from yours and mine.

Respectfully,


Lou Reed

PS I am not a professional programmer, but I have done a lot of programming as an engineer.

[JoeM]

You need to be very carefully when dealing with AND and OR and parentheses. If you have them in the wrong place, it might not return your intended results.
Note that:
(Condition1 OR Condition2) AND Condition3
is not the same as
Condition1 OR (Condition2 AND Condition3)
Things embedded deeper in the parentheses are evaluated first (so you need to back to your mathematic rules of basic logic and order of operations).

I feel that Access sometimes confuses/muddles by adding a lot more parentheses than are needed in WHERE clauses. So it sometimes helps to work through it and remove the unnecessary ones.

For example, it is not necessary to write:
(Properties.[ZIP Code])
You can just write:
Properties.[ZIP Code]

[JoeM]

One trick I like to use in writing these myself is the following:

Write out the Conditions you want (and order that they need to happen in) on paper.
Create the SQL code for all the different Conditions of the WHERE clause independent of each other.
Then combine them and add the parentheses based on the order they need to happen in.

[June7]

The tutorial correctly shows (although it still has unnecessary parens):

WHERE ((Properties.[Property Type] = "single family") OR
(Properties.[Property Type] = "townhouse")) AND
(Properties.[ZIP Code] Between 20850 And 20920);

You posted:
WHERE (((Properties.[Property Type])="single family") OR
(((Properties.[Property Type])="townhouse")) AND
((Properties.[ZIP Code]) Between 20850 And 20950));

Simplest would be:
WHERE (Properties.[Property Type] = "single family" OR
Properties.[Property Type] = "townhouse") AND
Properties.[ZIP Code] Between 20850 And 20920;

As Joe said, Access will throw in all those unneeded parens.

So be careful in the query design grid about building with OR and AND operators. Access won't always get the parens the way you expect.