Terry, depends on your db - * works in MS Access % works in Oracle
-Ben -----Original Message----- From: Terry Hogan [mailto:[EMAIL PROTECTED]] Sent: Wednesday, November 14, 2001 4:42 PM To: CF-Talk Subject: Re: Access returning 0 records (or "I hate #@$!@#! Jet SQL") Eric, I believe the correct LIKE operator is %, Not *. I'm not sure if that will solve the problem, but I would give it a try. Terry *********** REPLY SEPARATOR *********** On 11/14/2001 at 4:25 PM Maia, Eric wrote: >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 > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm 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
