Query Issue

[truitt20]

I have a simple crosstab query in Access. In column "Security" is a list of securities and their strike prices. The strike price always follows the text "k=" and i need to just grab the strike value and put it another field. Unfortunately the numbers of spaces before & after the strike vary. For example:

Bank of India Call k=4.1009 10/17/07
FTSE/JSE Tp40 Put k=22000.98 10/16/07 (jpm)


So essentially I need to figure out a way to grab the numbers after "k="

Any suggestions?

thanks

jt

 

[Remou]

Perhaps:

[tt]X = "FTSE/JSE Tp40 Put k=22000.98 10/16/07 (jpm)"
Y = Mid(Split(X, "k=")(1), 1, InStr(X, " "))[/tt]

 

[truitt20]

I am looking to write it as an expression in the Field name.

So the result for FTSE/JSE Tp40 Put k=22000.98 10/16/07 (jpm)

would be 22000.98

 

[Remou]

Try this in the query design grid:

[tt]Mid(Mid(X, InStr(X, "k") + 2), 1, InStr(X, " "))[/tt]

Change X to the name of the field with 'k='

 

[truitt20]

Almost there. For:

FTSE/JSE Tp40 Put k=22000.98 10/16/07 (jpm)


I changed the expression to:

Mid(Mid(X, InStr(X, "=") + 1), 1, InStr(X, " "))


and this gives me an output of 2

i need to include the whole number of 22000.98. and also this number varies in length

 

[Remou]

That is not the result that I get. Is there a space after the equals sign? Try:

[tt]Mid(Trim(Mid(X, InStr(X, "=") + 1)), 1, InStr(X, " "))[/tt]

 

[truitt20]

still just show the first number after the =


i have been playing around with it too and cannot get it to work

 

[Remou]

Please post the SQL.

 

[truitt20]

TRANSFORM Sum(JPM.[End Qty]) AS [End Qty]
SELECT JPM.Security, JPM.[Sec Symbol], Mid(Trim(Mid([Security],InStr([Security],"=")+1)),1,InStr([Security],"")) AS Strike, JPM.[Sec Currency ISO Code] AS Ccy
FROM JPM
GROUP BY JPM.Security, JPM.[Sec Symbol], Mid(Trim(Mid([Security],InStr([Security],"=")+1)),1,InStr([Security],"")), JPM.[Sec Currency ISO Code]
PIVOT JPM.[Fund Code];

 

[Remou]

The two quotes should enclose a space:

TRANSFORM Sum(JPM.[End Qty]) AS [End Qty]
SELECT JPM.Security, JPM.[Sec Symbol], Mid(Trim(Mid([Security],InStr([Security],"=")+1)),1,InStr([Security],"")) AS Strike, JPM.[Sec Currency ISO Code] AS Ccy
FROM JPM
GROUP BY JPM.Security, JPM.[Sec Symbol], Mid(Trim(Mid([Security],InStr([Security],"=")+1)),1,InStr([Security]," ")), JPM.[Sec Currency ISO Code]
PIVOT JPM.[Fund Code];

 

[truitt20]

Great - that pulls data now, but it is pulling data in various lengths. not just the strike. do you know how to just pull the strike?

For example:

FTSE/JSE Tp40 Put k=22000.98 10/16/07 (jpm)

is pulling:

22000.98 1

 

[Remou]

I missed including the first space, but your result puzzles me. Try this:

TRANSFORM Sum(JPM.[End Qty]) AS [End Qty]
SELECT JPM.Security, JPM.[Sec Symbol], Mid(Trim(Mid([Security],InStr([Security],"=")+1)),1,InStr([Security]," ")) AS Strike, JPM.[Sec Currency ISO Code] AS Ccy
FROM JPM
GROUP BY JPM.Security, JPM.[Sec Symbol], Mid(Trim(Mid([Security],InStr([Security],"=")+1)),1,InStr([Security]," ")), JPM.[Sec Currency ISO Code]
PIVOT JPM.[Fund Code];

 

[truitt20]

Ya - still the same results. The string length it is pulling varies for some reason. Sometimes it pulls a length of 4 and then in another row it may pull a length of 7. Not sure how to really fix it either. I have been tinkering with your expression, but t no avail.

 

[Remou]

Please post a few lines of data, rather than just the single sample.

 

[truitt20]

HSBC Put k=130 exp 8/30/07 (jpm) 130 e
HSCEI Put k=11097.9 11/29/07 (jpm) /drc 11097.
HSI Put k=19822.5 10/30/07 (jpm)/nw 1982
HSI Put k=21208.75 10/30/07 (jpm)/nw 2120
HSI Put k=21742.65 10/30/07 (jpm)/nw 2174
Huaneng Power Put k=7.72 9/13/07 (jpm) 7.72 9/1
Huaneng Power Put k=7.783 9/14/07 (jpm) 7.783 9/
Huaneng Power Put k=7.835 9/14/07 (jpm) 7.835 9/

First column is "Security"
Second column is "Strike"

thanks

 

[Remou]

Oops

[tt]Mid([Security], InStr([Security], "=") + 1, InStr(Mid([Security], InStr([Security], "=") + 1), " "))[/tt]

 

[truitt20]

YA!!! Working now!!

Thanks so much!

 

[PHV]

A simpler way:
Val(Mid([Security], InStr([Security], "=") + 1))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Remou]

PHV, when I tested a similar idea, I found that in my version of Access, the first digit from the date was included in the result.

I hope you had a break