SQL syntax

Hi guys, I have an error in my SQL somewhere. When I run...

"SELECT MESDATE,LOCATION,ALLOCATION,OWNERSHIPTYPE,PARTNBR,VERSION,PARTDESCRIPTION,QTY,TRANSTYPE,REASONCODE,REASONDESC,TRANSCOMMENT" & _
                    ",GRNNBR,SERIALNBR,CURRENCY,UNITCOST,TOTALBASELINECOST,COMPARTNBR FROM tblMestecData WHERE " & _
                    " MESDATE between '2011-07-17' AND '2011-08-13'

I get the correct data with the date filter. When I run

"SELECT MESDATE,LOCATION,ALLOCATION,OWNERSHIPTYPE,PARTNBR,VERSION,PARTDESCRIPTION,QTY,TRANSTYPE,REASONCODE,REASONDESC,TRANSCOMMENT" & _
                    ",GRNNBR,SERIALNBR,CURRENCY,UNITCOST,TOTALBASELINECOST,COMPARTNBR FROM tblMestecData WHERE " & _
                    " MESDATE between '2011-07-17' AND '2011-08-13' AND TRANSTYPE= 'Use' OR TRANSTYPE= 'Stock Adj' Order By MESDATE"

The date range is ignored?

Thanks,
Dean
deanlee17Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
because of the OR.

please use () around AND and ORed conditions
"SELECT MESDATE,LOCATION,ALLOCATION,OWNERSHIPTYPE,PARTNBR,VERSION,PARTDESCRIPTION,QTY,TRANSTYPE,REASONCODE,REASONDESC,TRANSCOMMENT" & _
                    ",GRNNBR,SERIALNBR,CURRENCY,UNITCOST,TOTALBASELINECOST,COMPARTNBR FROM tblMestecData WHERE " & _
                    " MESDATE between '2011-07-17' AND '2011-08-13' AND ( TRANSTYPE= 'Use' OR TRANSTYPE= 'Stock Adj' ) Order By MESDATE"

Open in new window

0
 
deanlee17Author Commented:
So id need the AND around the 2 dates too?..

(MESDATE between '2011-07-17' AND '2011-08-13' )?

Thanks
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
no, but it won't do any bad.
it's the () around the OR ...
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
deanlee17Author Commented:
Ok a little confused why i would put it around

( TRANSTYPE= 'Use' OR TRANSTYPE= 'Stock Adj' )

and not

MESDATE between '2011-07-17' AND '2011-08-13'

?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
let's explain it a bit with how sql will evaluate:

WHERE <condition1>  AND  TRANSTYPE= 'Use' OR TRANSTYPE= 'Stock Adj'
will execute like this:
WHERE ( <condition1>  AND  TRANSTYPE= 'Use' ) OR TRANSTYPE= 'Stock Adj'

so, it will return all the rows for TRANSTYPE = 'Stock Adj' for which the <condition 1> is ignored.


MESDATE between '2011-07-17' AND '2011-08-13'
is evaluated the same as
(MESDATE between '2011-07-17' AND '2011-08-13' )
resp:
MESDATE >= '2011-07-17' AND MESDATE <= '2011-08-13'
aka
(MESDATE >= '2011-07-17' AND MESDATE <= '2011-08-13')

hope this helps
0
 
deanlee17Author Commented:
That will do nicely, thanks for taking the time to explain that.

I now have to change those fixed dates for combo boxes on a form, but I guess thats for another post.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.