We help IT Professionals succeed at work.

Truncating string function

marellano
marellano asked
on
Hello Experts,

MS Access SQL help.

I'm trying to truncate a long string which contains 2 to 3 random sections with character.  Each section is separated by a Foward Slash (/).  Need to capture only the COMPANY NAME from the left of the field, TRANSACTION_NARR, as follow:

TRANSACTION_NARR:

COMPANY-A/RANDOM-CHRS1/RANDOM-CHRS2/RANDOM-CHRS3
COMPANY-B/RANDOM-CHRS1/RANDOM-CHRS2/
COMPANY-C/RANDOM-CHRS1/RANDOM-CHRS2/RANDOM-CHRS3

DESIRE RESULTS:

COMPANY-A
COMPANY-B
COMPANY-C

I tried using the following logic to create a calculated field but it only removes the 3rd section:

IIf([TRANSACTION_NARR] Is Not Null,(Left([TRANSACTION_NARR],(Len([TRANSACTION_NARR])-InStr(StrReverse([TRANSACTION_NARR]),"/")))),0)

Please provide full solution...greatly appreciated!

marellano
Comment
Watch Question

Owner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010
Commented:
IIF(IsNull([Transaction_Narr]), "", iif(instr([Transaction_Narr], "/" = 0, "", Left([Transaction_Narr], instr([Transaction_Narr], "/")-1)))

Author

Commented:
hi fyed, thanks but the function contained the wrong number of arguments.

IIF(IsNull([Transaction_Narr]), "", iif(instr([Transaction_Narr], "/" = 0, "", Left([Transaction_Narr], instr([Transaction_Narr], "/")-1)))

I assumed and placed a Close Parentheses ) in the following section:

 IIf(InStr([Transaction_Narr],"/")=0

Re-ran and got the same results as my original posting.  It is still not truncating everything from the first Foward Slash (/)

Author

Commented:
hi fyed...i take it back...it worked...i accidentally looked at some other results!!

Thanks a bunch!!

Author

Commented:
just had a Close Parentheses missing by accident.  Revised and worked
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
try this:

iif(instr(1,""  & Transaction_Narr,"/") > 0, mid(Transaction_Narr,1, instr(Transaction_Narr,"/") - 1 ), Transaction_Narr)

Explore More ContentExplore courses, solutions, and other research materials related to this topic.