extract item between quote marks

target string:
WHERE (((QrySMMTDataTechnical.[MVRIS CODE])="d8aaa") AND ((QrySMMTDataTechnical.[CW Aliased Marque])=[Client Aliased Marque]) AND ((((IIf(IsNull([ccd]),True,Abs([QryclientRefAndTechnicalDetail].[client cc]-[ccd])<=5))=True))=True) AND ((createtestclass([concatenated client],[model range],"modelcw"))>=1) AND ((Nz(createtestclass([client trans],[transmission],"Transmission"),0))>0) AND ((Nz(createtestclass([client fuel],[fuel],"fuel"),0))=1));

I want to extract the [mvris code] from the string how can i do this? the mvris code will change but is always after this QrySMMTDataTechnical.[MVRIS CODE])= and will always be 5 characters in length.
PeterBaileyUkAsked:
Who is Participating?
 
CluskittCommented:
Tried it, it works:

Sub temp()
CurrentQueryStr = "WHERE (((QrySMMTDataTechnical.[MVRIS CODE])=""d8aaa"") AND ((QrySMMTDataTechnical.[CW Aliased Marque])=[Client Aliased Marque]) AND ((((IIf(IsNull([ccd]),True,Abs([QryclientRefAndTechnicalDetail].[client cc]-[ccd])<=5))=True))=True) AND ((createtestclass([concatenated client],[model range],""modelcw""))>=1) AND ((Nz(createtestclass([client trans],[transmission],""Transmission""),0))>0) AND ((Nz(createtestclass([client fuel],[fuel],""fuel""),0))=1));"
MsgBox Mid(CurrentQueryStr, InStr(CurrentQueryStr, "QrySMMTDataTechnical.[MVRIS CODE])=") + 36, InStr(Mid(CurrentQueryStr, InStr(CurrentQueryStr, "QrySMMTDataTechnical.[MVRIS CODE])=") + 37), Chr(34)))
End Sub

Replace MsgBox with whatever var you want to store in, like strVar=, or something.
0
 
peter57rCommented:
I'm not sure that I have counted the 15 correctly- you might have to adjust it

value= mid(thisfieldorsqlname, instr(thisfieldorsqlname, "[MVRIS CODE])=")+15, 5)
0
 
PeterBaileyUkAuthor Commented:
The string has stuff before that and too much to be counted.

I am grabbing it like this:
Set qd = db.QueryDefs("QrySMMTclientTechnicalMatch")
CurrentQueryStr = qd.SQL

and now within the sql itself is that where clause with the target code.

0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
CluskittCommented:
sCode=Mid(sqlVar, InStr(sqlVar,"QrySMMTDataTechnical.[MVRIS CODE])=")+36, InStr(Mid(sqlVar,InStr,"QrySMMTDataTechnical.[MVRIS CODE])=")+37),Chr(34))-InStr(sqlVar,"QrySMMTDataTechnical.[MVRIS CODE])=")+36))

If I have not confused the ( and ), this should return anything between "" after QrySMMTDataTechnical.[MVRIS CODE])=, no matter how many chars it has.
0
 
PeterBaileyUkAuthor Commented:
here is the sql itself
SELECT QrySMMTDataTechnical.[MVRIS CODE], QrySMMTDataTechnical.[CW Aliased Marque], QryClientRefandTechnicalDetail.[Client Aliased Marque], QrySMMTDataTechnical.MARQUE, QrySMMTDataTechnical.[NOM CC], QrySMMTDataTechnical.[POWER KW], QrySMMTDataTechnical.[CALC BHP], QrySMMTDataTechnical.[MODEL RANGE], QrySMMTDataTechnical.[RANGE SERIES], QrySMMTDataTechnical.VARIANT, QrySMMTDataTechnical.[Concatenated CW Data], QrySMMTDataTechnical.[BODY TYPE], QrySMMTDataTechnical.[Calculated BHP], QrySMMTDataTechnical.[Calculated KW], QrySMMTDataTechnical.[DRIVING AXLE], QrySMMTDataTechnical.sCWDesc, QrySMMTDataTechnical.sCWDescGroup, QrySMMTDataTechnical.sCWTechnicalGroup, QrySMMTDataTechnical.[VEHICLE CATEGORY CODE], QrySMMTDataTechnical.[ENGINE MODEL], QrySMMTDataTechnical.ccd, QrySMMTDataTechnical.FUEL, QrySMMTDataTechnical.[VALVES PER CYLINDER], QrySMMTDataTechnical.[NO CYLINDERS], QrySMMTDataTechnical.[INTRO DATE], QrySMMTDataTechnical.[TERMINATION DATE], QrySMMTDataTechnical.[DRIVE TYPE], QrySMMTDataTechnical.CalcTer
minationDate, QrySMMTDataTechnical.DOORS, QrySMMTDataTechnical.TRANSMISSION, QrySMMTDataTechnical.[WHEELBASE TYPE], QrySMMTDataTechnical.[VAN ROOF CONFIG], QrySMMTDataTechnical.FDAspiration, QrySMMTDataTechnical.[CAB TYPE], QryClientRefandTechnicalDetail.ClientCode, QryClientRefandTechnicalDetail.[Client Make], QryClientRefandTechnicalDetail.[Client Model], QryClientRefandTechnicalDetail.[Client Intro], QryClientRefandTechnicalDetail.[Client Term], QryClientRefandTechnicalDetail.SERIES, QryClientRefandTechnicalDetail.[Client CC], QryClientRefandTechnicalDetail.[Client Doors], QryClientRefandTechnicalDetail.[Client Body], QryClientRefandTechnicalDetail.[Client Fuel], QryClientRefandTechnicalDetail.[Client Trans], QryClientRefandTechnicalDetail.SECURITY_STATUS, QryClientRefandTechnicalDetail.NEW_GROUP, QryClientRefandTechnicalDetail.GROUP_STATUS, QryClientRefandTechnicalDetail.GROUP_50, QryClientRefandTechnicalDetail.[Client Gross Weight], QryClientRefandTechnicalDetail.[Client Payload], QryClientRefandTechnic
alDetail.Stage, ((IIf(IsNull([ccd]),True,Abs([QryclientRefAndTechnicalDetail].[client cc]-[ccd])<=5))=True) AS CC_5, QryClientRefandTechnicalDetail.[Concatenated Client], createtestclass([concatenated client],[model range],"modelcw") AS ModelCWRank, createtestclass([concatenated client],[variant],"model") AS ModelRank, createtestclass([Client CC],[ccd],"CC") AS CCRank, createtestclass([Client doors],[doors],"doors") AS DoorRank, Nz(createtestclass([client trans],[transmission],"Transmission"),0) AS TransmissionRank, Nz(createtestclass([client fuel],[fuel],"fuel"),0) AS FuelRank, Nz(CreateTestClass([client intro],Nz([INTRO DATE],"1/1/1800"),"Date",[client term],Nz(IIf(IsNull([CalcTerminationDate]),[termination date],[CalcTerminationDate]),"1/1/1800")),0) AS DateRank, Nz(createtestclass([concatenated client],[Body Type],"BodyRev"),0) AS BodyStrRank, ([modelcwrank]*1)+([modelrank]*1)+([ccrank]*1)+([fuelrank]*1) AS [Total level 1], Nz(createtestclass([concatenated client],[Drivefromvariant],"Drivefwd"),0) AS Dri
veRevRank, Nz(createtestclass(removestringduplicates([concatenated client]),[Drivefromvariant],"drivefwd"),0) AS DriveStrRank, Nz(createtestclass([concatenated client],[No Cylinders],"Valves",[VALVES PER CYLINDER]),0) AS ValveRank, Nz(createtestclass([concatenated client],[Cab type],"Cab"),0) AS CabRank, Nz(createtestclass([concatenated client],[VAN ROOF CONFIG],"roof"),0) AS RoofRank, createtestclass([ClientBHPDerived],[calc bhp],"bhp") AS BhpStrRank, Nz(createtestclass([concatenated client],[WHEELBASE TYPE],"WheelBase"),0) AS WheelBaseRank, createtestclass([variant],0,"GetValvesStr") AS ValvesFromVariant, createtestclass([variant],0,"Drive") AS DriveFromVariant, createtestclass([variant],0,"GetStrNom") AS NomFromVariant, QryClientRefandTechnicalDetail.ClientBHPDerived, QrySMMTDataTechnical.[CAB TYPE], QrySMMTDataTechnical.[VAN ROOF CONFIG], ([cabrank]*1)+([wheelbaserank]*1)+([roofrank]*1) AS [Total level 3], 0 AS [Total level 4], ([doorrank]*1)+([bodystrrank]*1)+([transmissionrank]*1)+([cabrank]*1)+([wheel
baserank]*1)+([roofrank]*1)+([bodystrrank]*1)+([daterank]*1) AS [Total level 2], ([Total level 1]*1000000)+([total level 2]*100)+([total level 3]*10)+[total level 4] AS Total, QrySMMTDataTechnical.[VEHICLE CATEGORY CODE], IIf(([vehicle category code]>='c' And [vehicle category code]<='e'),1,0) AS CategoryType, QrySMMTDataTechnical.CalcTerminationDate, QrySMMTDataTechnical.[TERMINATION DATE]
FROM QrySMMTDataTechnical LEFT JOIN QryClientRefandTechnicalDetail ON QrySMMTDataTechnical.[CW Aliased Marque] = QryClientRefandTechnicalDetail.[Client Aliased Marque]
WHERE (((QrySMMTDataTechnical.[MVRIS CODE])="d8aaa") AND ((QrySMMTDataTechnical.[CW Aliased Marque])=[Client Aliased Marque]) AND ((((IIf(IsNull([ccd]),True,Abs([QryclientRefAndTechnicalDetail].[client cc]-[ccd])<=5))=True))=True) AND ((createtestclass([concatenated client],[model range],"modelcw"))>=1) AND ((Nz(createtestclass([client trans],[transmission],"Transmission"),0))>0) AND ((Nz(createtestclass([client fuel],[fuel],"fuel"),0))=1));

Open in new window

0
 
peter57rCommented:
The count starts from the [ before MVRIS (not the start of the whole sql string); that is all that would be adjusted.

You need to try it and see if it gives you the correct string
0
 
kraivenCommented:
This should do it:
 
Sub Extract()

Dim src As String

src = "WHERE (((QrySMMTDataTechnical.[MVRIS CODE])=""d8aaa"") AND ((QrySMMTDataTechnical.[CW Aliased Marque])=[Client Aliased Marque]) AND ((((IIf(IsNull([ccd]),True,Abs([QryclientRefAndTechnicalDetail].[client cc]-[ccd])<=5))=True))=True) AND ((createtestclass([concatenated client],[model range],""modelcw""))>=1) AND ((Nz(createtestclass([client trans],[transmission],""Transmission""),0))>0) AND ((Nz(createtestclass([client fuel],[fuel],""fuel""),0))=1));"



start = InStr(1, src, "QrySMMTDataTechnical.[MVRIS CODE])=""")
startpos = start + Len("QrySMMTDataTechnical.[MVRIS CODE])=""")

stoppos = InStr(startpos, src, """")

ExtractIt = Mid(src, startpos, stoppos - startpos)


End Sub

Open in new window

0
 
PeterBaileyUkAuthor Commented:
Kraiven the "d8aaa" is not constant when i look in the string the value will have changed
0
 
PeterBaileyUkAuthor Commented:
tried this but syntax errors
sCode=Mid(sqlVar, InStr(CurrentQueryStr,"QrySMMTDataTechnical.[MVRIS CODE])=")+ 36, InStr(Mid(CurrentQueryStr,InStr,"QrySMMTDataTechnical.[MVRIS CODE])=") +37),Chr(34))-InStr(CurrentQueryStr,"QrySMMTDataTechnical.[MVRIS CODE])=") +36))
0
 
CluskittCommented:
Should be something like this, then:

sCode = Mid(CurrentQueryStr, InStr(CurrentQueryStr, "QrySMMTDataTechnical.[MVRIS CODE])=") + 36, InStr(Mid(CurrentQueryStr, InStr(CurrentQueryStr, "QrySMMTDataTechnical.[MVRIS CODE])=") + 37), Chr(34)) - InStr(CurrentQueryStr, "QrySMMTDataTechnical.[MVRIS CODE])=") + 36)
0
 
PeterBaileyUkAuthor Commented:
Its closer i get runtime error 5 Invalid procedure call or argument.
0
 
CluskittCommented:
The theory is:

Mid(CurrentQueryStr, a, b-a)
where a is the first char after QrySMMTDataTechnical.[MVRIS CODE])="
And b is the first " after AfterQrySMMTDataTechnical.[MVRIS CODE])="
0
 
PeterBaileyUkAuthor Commented:
excellent
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.