Thanks!!! to all - the % worked. (I thought I tried it first, but I may have had some other problem with my query then...) Eric
-----Original Message----- From: Terry Hogan [mailto:[EMAIL PROTECTED]] Sent: Wednesday, November 14, 2001 5:12 PM To: CF-Talk Subject: RE: Access returning 0 records (or "I hate #@$!@#! Jet SQL") Ben, IN Access, the * works as the wildcard, but when working with cfquery you need to use %. I'm not sure if this is a CF, SQL, or ODBC restriction, but that's the way it is... Terry *********** REPLY SEPARATOR *********** On 11/14/2001 at 6:47 PM Braver, Ben wrote: >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 >> >> >> > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.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
