select where a AND (b or c) - can't get brackets in right place 2

[emozley]

Hi,

I am trying to select some records where the userID=124 and it is in a certain date range - between 1st november 2005 year and 31st october 2006) I am almost there but I have got my brackets in the wrong place so it is returning all records. Have been looking at it all day so a fresh set of eyes would be much appreciated!

Thanks very much

Ed

SELECT CPDID, CPDDate, CPDDescription, CPDPoints, Location, Accredited FROM CPD WHERE UserID=124 AND ((DatePart('m', CPDDate)>10 AND DatePart('yyyy', CPDDate)=2005)) OR (DatePart('m', CPDDate)<11 AND DatePart('yyyy', CPDDate)=2006)

 

[traingamer]

Why not simply

SELECT CPDID, CPDDate, CPDDescription, CPDPoints, Location, Accredited FROM CPD WHERE UserID=124 AND CPDDate between #11/01/2005# and #10/31/2006#

or whatever the date settings are on your machine e.g. 2005/11/01, etc.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[SkipVought]

Hi,

or

SELECT CPDID, CPDDate, CPDDescription, CPDPoints, Location, Accredited FROM CPD WHERE UserID=124 AND Format(CPDDate,'yyyymm') between '200511' and '200610'

Skip,

_[red][/red]

 

[emozley]

Brilliant! Was not aware of the between function but that will make things a million times easier from now on.

cheers

Ed