SQL Mid() Synax

[CaptMitch]

So I am helping a friend and client who self-publishes data from an Access database.  I have an amateur background in programming and have been able to handle many things for him but am by no means a VBA or SQL expert. I will be trying to help him find someone who is, as some of his needs are above my level. 

We need to extract specified strings from a large text field and insert into another field. I am working on just one string, but if I can get it to work for one, the others will be easy.

Here is the Query that finds the records containing the desired string. It works. 

SELECT [Armenian Titles].biography, [Armenian Titles].Translator

FROM [Armenian Titles]

WHERE InStr([Biography],"Translator: ") >1

The next step is to get the next 40 characters following Translator:(+space char) and insert it into the Translator field.

I tried this and various minor changes, gives errors like missing operator or syntax error comma

Using a straight SET...="Text" works fine.

UPDATE [Armenian Titles] SET [Translator] = Mid([Biography], InStr(([Biography],"Translator: ")+12), 40);

WHERE InStr([Biography],"Translator: ") >1

I a

[#2]

If it is true that "Using a straight SET...="Text" works fine", what problem remains to be solved?

[#3 · Accepted solution]

You have an extra parenthesis in your InStr formula.
The second clause of your Mid formula should be:
InStr([Biography],"Translator: ") +12,

So, altogether:
UPDATE [Armenian Titles] SET [Translator] = Mid([Biography], InStr([Biography],"Translator: ")+12, 40);
WHERE InStr([Biography],"Translator: ") >1

[#4]

Thanks so much. I did look at the () but obviously not well enough. The true end of the string I needed was the first period after the text searched for. I am sure there is a slicker way to find it, but this worked as a step 2:
UPDATE [Armenian Titles] SET Translator = Left([Translator], InStr([Translator],".")-1)
WHERE Len([Translator]) >1
Now on to Editor:, Publisher: and a few more. If I knew how to program VBA I could write a DO WHILE to do in one step but I'm too old and too retired to learn new languages.

[#5]

@CaptMitch 

You probably don't even need a loop.

And update query can update more than one field at a time. So, you can replicate the syntax for THIS field for all of the other elements you need to extract by modifying the start and length arguments to find them.

[#6]

@George Hepworth 

I thought that would work so that was exactly what I was going to try today.  In past, I had added multiple fields in one data definition query, but maybe after a version update, got a message saying can only add one at a time?

ALTER TABLE [Armenian Titles] ADD column [Translator] TEXT