Extract part of field

[Guest]

Hello,

I have been using the following statement based on posts in this newsgroup.

DESCR:
LTrim(Mid(Left([Description],InStr([Description],"(")-1),InStr([Description],":")+1))

Normally I need to see everything after the : and before the (

In today's case, there is no ( so I just need to see everything after the :

I've used IIF statements, but not sure how to incorporate one into this
statement.

Please help. Thanks, Mary

 

[Guest]

You can try the split function to display every thing after the :

split([Description],":")(1)

The 1 will give the second location, the problem with it, if you have two :
in the description, then it will diaplay only what in between

or you can use
mid([Description],instr([Description],":")+1)

 

[Guest]

Ofer, that works when there's no parentheses. What I really need is a
statement that will look for the parentheses anywhere in the string before
deciding which of the following to use:

If there are parentheses, use:
Trim(Mid(Left([Description],InStr([Description],"(")-1),InStr([Description],":")+1))

and if there are not parentheses, use:
Trim(Mid([Description],instr([Description],":")+1))

Thanks again, Mary

You can try the split function to display every thing after the :

split([Description],":")(1)

The 1 will give the second location, the problem with it, if you have two :
in the description, then it will diaplay only what in between

or you can use
mid([Description],instr([Description],":")+1)

Hello,

I have been using the following statement based on posts in this newsgroup.

DESCR:
LTrim(Mid(Left([Description],InStr([Description],"(")-1),InStr([Description],":")+1))

Normally I need to see everything after the : and before the (

In today's case, there is no ( so I just need to see everything after the :

I've used IIF statements, but not sure how to incorporate one into this
statement.

Please help. Thanks, Mary

 

[Guest]

Try this

IIF(Instr([Description],"(")>0,
Trim(Mid(Left([Description],InStr([Description],"(")-1),InStr([Description],":")+1)),Trim(Mid([Description],instr([Description],":")+1)))

Ofer, that works when there's no parentheses. What I really need is a
statement that will look for the parentheses anywhere in the string before
deciding which of the following to use:

If there are parentheses, use:
Trim(Mid(Left([Description],InStr([Description],"(")-1),InStr([Description],":")+1))

and if there are not parentheses, use:
Trim(Mid([Description],instr([Description],":")+1))

Thanks again, Mary

You can try the split function to display every thing after the :

split([Description],":")(1)

The 1 will give the second location, the problem with it, if you have two :
in the description, then it will diaplay only what in between

or you can use
mid([Description],instr([Description],":")+1)

Hello,

I have been using the following statement based on posts in this newsgroup.

DESCR:
LTrim(Mid(Left([Description],InStr([Description],"(")-1),InStr([Description],":")+1))

Normally I need to see everything after the : and before the (

In today's case, there is no ( so I just need to see everything after the :

I've used IIF statements, but not sure how to incorporate one into this
statement.

Please help. Thanks, Mary