Try using the InStr() function instead: SELECT Left(Trim([Company]),InStr(1,Trim([Company])," County")-1) AS Name FROM CCI_Contacts WHERE ((Right(Trim([Company]),6)="County"));
Take the Trim() if you know for sure that there are no trailing spaces. Also used Right() instead of Like in case you have a company or organization name with a County in the middle. Srini _____________________________________________ P. Srinivasan CertainTech, Inc. Sterling, Virginia, USA Web: http://www.certaintech.com Email: [EMAIL PROTECTED] ----- Original Message ----- From: "Bill Thoen" <[EMAIL PROTECTED]> To: "MapInfo-L" <[email protected]> Sent: February 02, 2005 6:12 PM Subject: MI-L OT - Access SQL Question I have an MS Access table with a text column called Company, and it contains names like "Baca County", "Jefferson County", "Boulder County", etc. What I want to do is write an Access SQL query to return the names as "Baca", "Jefferson", "Boulder", etc. I tried this: SELECT LEFT(Company, SEARCH(" County", Company, 1)) as Name FROM CCI_Contacts WHERE Company LIKE "*County*"; and Access whines about an "undefined fucntion 'SEARCH' in expression," yet SEARCH() is a string function that returns a number and is documented in the online help. Anyone know what's wrong with this? --------------------------------------------------------------------- List hosting provided by Directions Magazine | www.directionsmag.com | To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] Message number: 15114 --------------------------------------------------------------------- List hosting provided by Directions Magazine | www.directionsmag.com | To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] Message number: 15115
