Spliting info

[Leesha]

Hi,
I have column with data that looks like similar to this:

M1302_RISK_OF_PRSR_ULCR

I need to slit out the first part in each row, when the first letter in the string is M. Otherwise it would remain the same. When the first letter starts with "M" it will always be 5 characters before the _. Each cell is differnt and may be much longer than this example. The one consistent thing is that the info I need is always at the beginning of the string.

Thanks!
Leesha

[HansV]

Let's say the field is named MyField.
The expression you need would be

IIf([Left([MyField],1)="M",Mid([MyField],6),[MyField])

[Leesha]

Thanks Hans!

I must be doing something wrong because I'm getting an erorr "the expression you entered contains invalid syntax, or you need to enclose your text in quotes.

This is my query expression:
M0Answer:IIf([Left([form_location],1)="M",Mid([form_location],6),[form_location])
Thanks
Leesha

[HansV]

Sorry, a typo. There shouldn't have been a [ before Left:

M0Answer:IIf(Left([form_location],1)="M",Mid([form_location],6),[form_location])

[Leesha]

Thanks! That took care of the error. When I ran the code it returned the info after the M0 piece (I'm sure my request didn't make sense). So instead of returning M1302_RISK_OF_PRSR_ULCR it gave me RISK_OF_PRSR_ULCR. I expreimented with the code by changing "Mid" to Left and changed the 6 to a 5 and it worked!!! I couldn't have done that if it hadn't been all of these years with your teaching me!!
Thanks,
Leesha

[HansV]

I had interpreted your request differently, but I'm glad you were able to modify the formula!

[Leesha]

Only because I have such a patient teacher!!!

Thanks again,
Leesha