using the like operator with the Instr function

[leahyj]

Anybody have an idea how to use the Like operator within the Instr function. Example:
Left([CityStateZip],Instr(1,[CityStateZip], Like "* ?? *"))

Field [CityStateZip] value = COVINGTON KY 41011-1227

 

[PHV]

Have you tried this ?
Split([CityStateZip])(1)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[leahyj]

Well,

Thanks for the Split tip, but I don't have the Split function available and in my many years coding with vba using access, I have never heard or seen split.

 

[EBGreen]

What version of Access are you using? I don't think it is available in 97, but It definitely is in 2003 and I think it is in 2000.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[leahyj]

Unfortunately,

version 97 at work, where I need it and at home I have xp.

 

[EBGreen]

If you search the vba fora, you should find a few examples of writing a roll your own Split function.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[leahyj]

I am all set. Thanks to everyone. I ended up doing what I know already.

City: Mid([CityStateZip],1,InStr(1,[CityStateZip],"-")-9)