Hi Jason,
I need to have both AND and OR type search results included. My client wanted the search to allow users to enter multiple criteria and not be restricted to all AND type search results e.g. if they incorrectly entered one piece of data, but entered another correctly it would still find a match on the correctly entered field value.
I built a safeguard in to retrieve results only for the currently logged in user by specifying an AND search on the ACCOUNT_REF value, which is a field in the database matched to a session variable that gets set once the user has logged in. However, this seems to be being ignored when using Date range and any other criteria.
I did as you suggested and wrote the SQL query to the page and got the following:
SQL: SELECT * FROM stg_ordersummary WHERE (ORDER_DATE >= "2011-07-01 00:00:00") AND (ORDER_DATE <= "2011-07-01 23:59:59") OR ((CUST_ORDER_NUMBER = '13735')) AND ((ACCOUNT_REF = 'BWEUROPE')) ORDER BY ORDER_DATE DESC
However, records were returned that were outside of the specified date range. Is this down to the fact that my SQL is only applying the AND operator to the dates and then saying - ok no match there, but there's this OR value on the CUST_ORDER_NUMBER field and I've found records that match that field - so that's what's displaying??
Still puzzled as to why I'm getting records returned for other users though who I'm not logged in as????
