dw8 select statement woes

[tamak]

Im trying to perfom a search on the following criteria
- date of the event (in my access database) is BETWEEN a start and finish date
- a 'showthis?' type of value is set to YES
- (optionally) the detail/notes of the event matches a user-entered text string (using % as default though)...

Now, when I simplify the sql select statement (say just using the BETWEEN dates portion of the code) it works fine and finds only those matches.... when I make the select statemenet more complex though, it just returns ALL records.


I cant figure this out - what should i change? heres the select statement:


SELECT *
FROM tbl_events
WHERE showonsite Like 'MMColParam5' and (eventdate BETWEEN
#MMColParam2# AND #MMColParam3# And eventtitle like '%MMColParam4%' or eventdetail like '%MMColParam4%')


(in the recordset dialogue window I have the default values set like this
MMColParam 2 startdate (default value - 1/1/1989)
MMColParam 3 enddate (default value - 1/1/2008)
MMColParam5 Yes (default value set to YES)
MMColParam4 %


why is it choking with a more complex select statement? I added parenthesis after reading online that this would help with more complex statements.

 

[Cheech]

I don't know if it will help but I have always had loads of issues with dates when I used to use Access so I used to build the dates up using this code.

strDate = Date()
dd=DatePart("d", strDate)
if len(dd)=1 then dd= "0" & dd end if
mm=DatePart("m", strDate)
if len(mm)=1 then mm= "0" & mm end if
yy=DatePart("yyyy", strDate)
if len(yy)=2 then yy= "20" & yy end if
strDate = dd & "/" & mm & "/" & yy

[Peace][Pipe]

 

[traingamer]

What do you get if you remove just the OR clause?

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

 

[Cheech]

Didnt even see that, your select statement will read something along the lines of

SELECT * 
FROM tbl_events 
WHERE showonsite Like 'Yes' and (eventdate BETWEEN 
#1/1/1989# AND #1/1/2008# And eventtitle like '%%%' or eventdetail like '%%%')

therefore your "OR clause will always be returned as all eventdetails will be returned. Its a good idea sometimes to just Response.Write the select statement to the page to see what is actually being called for.


[Peace][Pipe]