Solved

extract item between quote marks

Posted on 2010-11-15
13
301 Views
Last Modified: 2012-05-10
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.
0
Comment
Question by:PeterBaileyUk
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 34135274
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
 

Author Comment

by:PeterBaileyUk
ID: 34135297
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
 
LVL 18

Expert Comment

by:Cluskitt
ID: 34135312
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
 

Author Comment

by:PeterBaileyUk
ID: 34135316
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
 
LVL 77

Expert Comment

by:peter57r
ID: 34135353
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
 
LVL 3

Expert Comment

by:kraiven
ID: 34135354
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:PeterBaileyUk
ID: 34135383
Kraiven the "d8aaa" is not constant when i look in the string the value will have changed
0
 

Author Comment

by:PeterBaileyUk
ID: 34135423
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
 
LVL 18

Expert Comment

by:Cluskitt
ID: 34135437
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
 

Author Comment

by:PeterBaileyUk
ID: 34135452
Its closer i get runtime error 5 Invalid procedure call or argument.
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 34135457
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
 
LVL 18

Accepted Solution

by:
Cluskitt earned 500 total points
ID: 34135485
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
 

Author Closing Comment

by:PeterBaileyUk
ID: 34135768
excellent
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now