I have an Access DB set up as an ODBC datasource (on NT 4). I am building a dynamic SELECT statement based on a number of fields in the search form. The form includes several <select> fields and one text entry field to enter keywords. The query works fine as long as only the select fields are used (they are populated from the database).
However, when I enter keywords in the text entry field, I get no records returned. I can cut and paste the generated SQL statement into a query in the Access database, and it will return records. I am using the PreserveSingleQuotes function, so that's not the problem. As far as I can tell from the Access help files, my SQL statement is properly constructed. Can anyone see what I'm doing wrong? I think it must be in my use of LIKE, but I'm stumped. Here's an example SQL statement generated by my template. This returns 0 records via ColdFusion, but returns 1 records when pasted as a query in Access: SELECT DISTINCT O.OrgID, O.Organization, O.City, O.Watersheds FROM Organizations O INNER JOIN (OrgActivities OA INNER JOIN OrgWatersheds OW ON OA.OrgID = OW.OrgID) ON O.OrgID = OA.OrgID WHERE 1=1 AND (O.Organization LIKE '*king*' OR O.SubOrg LIKE '*king*') ORDER BY Organization Thanks - Eric ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
