SOLVED

SQL Mid() Synax

%3CLINGO-SUB%20id%3D%22lingo-sub-3249968%22%20slang%3D%22en-US%22%3ESQL%20Mid()%20Synax%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3249968%22%20slang%3D%22en-US%22%3E%3CP%3ESo%20I%20am%20helping%20a%20friend%20and%20client%20who%20self-publishes%20data%20from%20an%20Access%20database.%26nbsp%3B%20I%20have%20an%20amateur%20background%20in%20programming%20and%20have%20been%20able%20to%20handle%20many%20things%20for%20him%20but%20am%20by%20no%20means%20a%20VBA%20or%20SQL%20expert.%20I%20will%20be%20trying%20to%20help%20him%20find%20someone%20who%20is%2C%20as%20some%20of%20his%20needs%20are%20above%20my%20level.%26nbsp%3B%3C%2FP%3E%3CP%3EWe%20need%20to%20extract%20specified%20strings%20from%20a%20large%20text%20field%20and%20insert%20into%20another%20field.%20I%20am%20working%20on%20just%20one%20string%2C%20but%20if%20I%20can%20get%20it%20to%20work%20for%20one%2C%20the%20others%20will%20be%20easy.%3C%2FP%3E%3CP%3EHere%20is%20the%20Query%20that%20finds%20the%20records%20containing%20the%20desired%20string.%20It%20works.%26nbsp%3B%3C%2FP%3E%3CP%3ESELECT%20%5BArmenian%20Titles%5D.biography%2C%20%5BArmenian%20Titles%5D.Translator%3C%2FP%3E%3CP%3EFROM%20%5BArmenian%20Titles%5D%3C%2FP%3E%3CP%3EWHERE%20InStr(%5BBiography%5D%2C%22Translator%3A%20%22)%20%26gt%3B1%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20next%20step%20is%20to%20get%20the%20next%2040%20characters%20following%20Translator%3A(%2Bspace%20char)%20and%20insert%20it%20into%20the%20Translator%20field.%3C%2FP%3E%3CP%3EI%20tried%20this%20and%20various%20minor%20changes%2C%20gives%20errors%20like%20missing%20operator%20or%20syntax%20error%20comma%3C%2FP%3E%3CP%3EUsing%20a%20straight%20SET...%3D%22Text%22%20works%20fine.%3C%2FP%3E%3CP%3EUPDATE%20%5BArmenian%20Titles%5D%20SET%20%5BTranslator%5D%20%3D%20Mid(%5BBiography%5D%2C%20InStr((%5BBiography%5D%2C%22Translator%3A%20%22)%2B12)%2C%2040)%3B%3C%2FP%3E%3CP%3EWHERE%20InStr(%5BBiography%5D%2C%22Translator%3A%20%22)%20%26gt%3B1%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20a%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3249968%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3252050%22%20slang%3D%22en-US%22%3ERe%3A%20SQL%20Mid()%20Synax%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3252050%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F126074%22%20target%3D%22_blank%22%3E%40George%20Hepworth%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20thought%20that%20would%20work%20so%20that%20was%20exactly%20what%20I%20was%20going%20to%20try%20today.%26nbsp%3B%20In%20past%2C%20I%20had%20added%20multiple%20fields%20in%20one%20data%20definition%20query%2C%20but%20maybe%20after%20a%20version%20update%2C%20got%20a%20message%20saying%20can%20only%20add%20one%20at%20a%20time%3F%3C%2FP%3E%3CP%3EALTER%20TABLE%20%5BArmenian%20Titles%5D%20ADD%20column%20%5BTranslator%5D%20TEXT%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3251778%22%20slang%3D%22en-US%22%3ERe%3A%20SQL%20Mid()%20Synax%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3251778%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1326665%22%20target%3D%22_blank%22%3E%40CaptMitch%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20probably%20don't%20even%20need%20a%20loop.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EAnd%20update%20query%20can%20update%20more%20than%20one%20field%20at%20a%20time.%20So%2C%20you%20can%20replicate%20the%20syntax%20for%20THIS%20field%20for%20all%20of%20the%20other%20elements%20you%20need%20to%20extract%20by%20modifying%20the%20start%20and%20length%20arguments%20to%20find%20them.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3251288%22%20slang%3D%22en-US%22%3ERe%3A%20SQL%20Mid()%20Synax%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3251288%22%20slang%3D%22en-US%22%3EThanks%20so%20much.%20I%20did%20look%20at%20the%20()%20but%20obviously%20not%20well%20enough.%20The%20true%20end%20of%20the%20string%20I%20needed%20was%20the%20first%20period%20after%20the%20text%20searched%20for.%20I%20am%20sure%20there%20is%20a%20slicker%20way%20to%20find%20it%2C%20but%20this%20worked%20as%20a%20step%202%3A%3CBR%20%2F%3EUPDATE%20%5BArmenian%20Titles%5D%20SET%20Translator%20%3D%20Left(%5BTranslator%5D%2C%20InStr(%5BTranslator%5D%2C%22.%22)-1)%3CBR%20%2F%3EWHERE%20Len(%5BTranslator%5D)%20%26gt%3B1%3CBR%20%2F%3ENow%20on%20to%20Editor%3A%2C%20Publisher%3A%20and%20a%20few%20more.%20If%20I%20knew%20how%20to%20program%20VBA%20I%20could%20write%20a%20DO%20WHILE%20to%20do%20in%20one%20step%20but%20I'm%20too%20old%20and%20too%20retired%20to%20learn%20new%20languages.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3250310%22%20slang%3D%22en-US%22%3ERe%3A%20SQL%20Mid()%20Synax%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3250310%22%20slang%3D%22en-US%22%3EYou%20have%20an%20extra%20parenthesis%20in%20your%20InStr%20formula.%3CBR%20%2F%3EThe%20second%20clause%20of%20your%20Mid%20formula%20should%20be%3A%3CBR%20%2F%3EInStr(%5BBiography%5D%2C%22Translator%3A%20%22)%20%2B12%2C%3CBR%20%2F%3E%3CBR%20%2F%3ESo%2C%20altogether%3A%3CBR%20%2F%3EUPDATE%20%5BArmenian%20Titles%5D%20SET%20%5BTranslator%5D%20%3D%20Mid(%5BBiography%5D%2C%20InStr(%5BBiography%5D%2C%22Translator%3A%20%22)%2B12%2C%2040)%3B%3CBR%20%2F%3EWHERE%20InStr(%5BBiography%5D%2C%22Translator%3A%20%22)%20%26gt%3B1%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3250242%22%20slang%3D%22en-US%22%3ERe%3A%20SQL%20Mid()%20Synax%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3250242%22%20slang%3D%22en-US%22%3EIf%20it%20is%20true%20that%20%22Using%20a%20straight%20SET...%3D%22Text%22%20works%20fine%22%2C%20what%20problem%20remains%20to%20be%20solved%3F%3C%2FLINGO-BODY%3E
New Contributor

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

5 Replies
If it is true that "Using a straight SET...="Text" works fine", what problem remains to be solved?
best response confirmed by CaptMitch (New Contributor)
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
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.

@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.

@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