Forum Discussion
Solved
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...
- 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
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
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.
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.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
