Extracting a substring from a text field

Question

Hi I have a table which contains a field with this example data

Corporate Services - All (E0)

Children and Families - All (A0)

Mental Health Services - Acute & Urgent Care (C3)

Mental Health Services - Acute & Urgent Care (C3)

Corporate Services - All (E0)

Corporate Services - All (E0)

Children and Families - All (A0)

Children and Families - All (A0)

Specialist Services - All (D0)

Community Health Services - West Network (B2)

Mental Health Services - Community MH (C4)

I want to write a formula in a query that will return only the textafter the hyphen "-".  E.g for "Mental Health Services - Acute & Urgent Care (C3)" to return "Acute & Urgent Care (C3)".

I've played with calculating the strings length then using InStrRev to calculate hyphen position and using Left$ etc. .  But can seem to get it right.

ANy suggestions

Trevor

Answer 1

I want to write a formula in a query that will return only the textafter the hyphen "-".  

Forgot what I read yesterday and thought it was text before.

Answer 2

Nope: InStr("ABCD - EFG", "-") will return 6; you want bytes 7 to the end.

Answer 3

Should not it be minus one?

Trim(Mid([Fieldname]), InStr([fieldname], "-" - 1))

Answer 4

Not sure why you need InStrRev  rather than InStr unless you might have records with more than one hyphen. The simplest solution would be just

Trim(Mid([Fieldname]), InStr([fieldname], "-" + 1))

The Trim will remove the blank after the hyphen, or you could use + 2 instead of +1 if the hyphen is ALWAYS consistently followed by exactly one blank. 

Note that the Mid() function takes three arguments (the string from which you're extracting, the start position, and the end position); but if you leave off the optional third argument it will give you the rest of the string, however long that might be.

The expression should work using InStrRev instead of InStr if you prefer.

Answer 5

That is indeed how you would do that, so study the InStrRev function and you should be able to do it.