InStr function to split column

angerplea

Registered User.
Local time
Yesterday, 20:07
Joined
Apr 18, 2012
Messages
22
Hi - Trying to split this up through Access 2010 query.

Example: "COMPANY FF - DAVE GROHL"

String lenth is variable and delimiter is "-". I have this working for one side.

CONTACT: Trim(IIf([SCOMPANY] Like "*-*",Right([scompany],Len([scompany])-InStr(1,[SCOMPANY],"-")),"."))

Can not get the company side to work?:banghead:

COMPANY: IIf([SCOMPANY] Like "*-*",Left([scompany],Len([scompany])-InStr(1,[SCOMPANY],"-")-2),[SCOMPANY])

Any thoughts??

Thanks
 
Use Mid() function instead, easier

Code:
COMPANY: IIf([SCOMPANY] Like "*-*", Mid([SCOMPANY],1, Instr([SCOMPANY],"-")-2), [SCOMPANY])

The correct syntax for Left() would be:

Code:
COMPANY: IIf([SCOMPANY] Like "*-*",Left([scompany],Len([scompany])-InStr(1,[SCOMPANY],"-")[COLOR="Red"]+1[/COLOR]),[SCOMPANY])

JR
 
Last edited:
Yes!!:D

Many thanks JR
 

Users who are viewing this thread

Back
Top Bottom