Solved

extract item between quote marks

Posted on 2010-11-15
13
332 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

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
 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Email question 19 93
Queries: Select, then Append, then Delete 8 41
Batch convert csv to xlsx 10 60
nested if statement in access with null values 2 11
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

751 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