Well, well... Instr() works! It didn't show up when I asked the stupid "Answer Wizard" and his bloody useless sidekick "Clippy" (the paperclip) for a list of "String Functions," but sure enough, if you already knwo what to look for, it's right there under "Instr()."
Thanks! - Bill Thoen On Wed, 2 Feb 2005, P. Srinivasan wrote: > 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: 15116
