Forum Discussion
SQL Mid() Synax
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
- 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
- If it is true that "Using a straight SET...="Text" works fine", what problem remains to be solved?
- 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- 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.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.
