PeterBaileyUk
asked on
extract item between quote marks
target string:
WHERE (((QrySMMTDataTechnical.[M VRIS CODE])="d8aaa") AND ((QrySMMTDataTechnical.[CW Aliased Marque])=[Client Aliased Marque]) AND ((((IIf(IsNull([ccd]),True ,Abs([Qryc lientRefAn dTechnical Detail].[c lient cc]-[ccd])<=5))=True))=Tru e) AND ((createtestclass([concate nated client],[model range],"modelcw"))>=1) AND ((Nz(createtestclass([clie nt trans],[transmission],"Tra nsmission" ),0))>0) AND ((Nz(createtestclass([clie nt 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.[MVRI S CODE])= and will always be 5 characters in length.
WHERE (((QrySMMTDataTechnical.[M
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.[MVRI
ASKER
The string has stuff before that and too much to be counted.
I am grabbing it like this:
Set qd = db.QueryDefs("QrySMMTclien tTechnical Match")
CurrentQueryStr = qd.SQL
and now within the sql itself is that where clause with the target code.
I am grabbing it like this:
Set qd = db.QueryDefs("QrySMMTclien
CurrentQueryStr = qd.SQL
and now within the sql itself is that where clause with the target code.
sCode=Mid(sqlVar, InStr(sqlVar,"QrySMMTDataT echnical.[ MVRIS CODE])=")+36, InStr(Mid(sqlVar,InStr,"Qr ySMMTDataT echnical.[ MVRIS CODE])=")+37),Chr(34))-InS tr(sqlVar, "QrySMMTDa taTechnica l.[MVRIS CODE])=")+36))
If I have not confused the ( and ), this should return anything between "" after QrySMMTDataTechnical.[MVRI S CODE])=, no matter how many chars it has.
If I have not confused the ( and ), this should return anything between "" after QrySMMTDataTechnical.[MVRI
ASKER
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));
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
You need to try it and see if it gives you the correct string
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
ASKER
Kraiven the "d8aaa" is not constant when i look in the string the value will have changed
ASKER
tried this but syntax errors
sCode=Mid(sqlVar, InStr(CurrentQueryStr,"Qry SMMTDataTe chnical.[M VRIS CODE])=")+ 36, InStr(Mid(CurrentQueryStr, InStr,"Qry SMMTDataTe chnical.[M VRIS CODE])=") +37),Chr(34))-InStr(Curren tQueryStr, "QrySMMTDa taTechnica l.[MVRIS CODE])=") +36))
sCode=Mid(sqlVar, InStr(CurrentQueryStr,"Qry
Should be something like this, then:
sCode = Mid(CurrentQueryStr, InStr(CurrentQueryStr, "QrySMMTDataTechnical.[MVR IS CODE])=") + 36, InStr(Mid(CurrentQueryStr, InStr(CurrentQueryStr, "QrySMMTDataTechnical.[MVR IS CODE])=") + 37), Chr(34)) - InStr(CurrentQueryStr, "QrySMMTDataTechnical.[MVR IS CODE])=") + 36)
sCode = Mid(CurrentQueryStr, InStr(CurrentQueryStr, "QrySMMTDataTechnical.[MVR
ASKER
Its closer i get runtime error 5 Invalid procedure call or argument.
The theory is:
Mid(CurrentQueryStr, a, b-a)
where a is the first char after QrySMMTDataTechnical.[MVRI S CODE])="
And b is the first " after AfterQrySMMTDataTechnical. [MVRIS CODE])="
Mid(CurrentQueryStr, a, b-a)
where a is the first char after QrySMMTDataTechnical.[MVRI
And b is the first " after AfterQrySMMTDataTechnical.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
excellent
value= mid(thisfieldorsqlname, instr(thisfieldorsqlname, "[MVRIS CODE])=")+15, 5)