[IncDist]

Hi All

I am trying to write a small app to take some text i paste into a textbox, remove any spaces and every third line, then keep only the first six characters of each line and output them inside some text.

My first hurdle is getting rid of the spaces. Here is the text I would be pasting in below. In the email it comes in it is in a table style format but when copied and pasted it puts the text into the texbox in the same style as below:

91PO.L
XS0687555572
B71CBK0

12GN.L
SE0004058444
B3XQFV1

Now what I want the app to do is:

91PO.LXS0687555572B71CBK0
12GN.LSE0004058444B3XQFV1

Then remove all but the first six characters:
91PO.L
12GN.L

So It can then finally output for me:

select FIM, FTI_UKIRISH_STAMP_INDICATOR from SECURITY where FIM='91PO.L'
select FIM, FTI_UKIRISH_STAMP_INDICATOR from SECURITY where FIM='12GN.L'

The idea being I can paste this into MySQL and get some values returned.

Private Sub CommandButton1_Click()
Dim Fim As String

'Removes Spaces
Fim = Trim(TextBox1.Value)
Fim = Fim.Replace(Chr(10), Chr(0))
Fim = Fim.Replace(Chr(13), Chr(0))

'Here to show what the above code has actually done
MsgBox Fim

'Taking first 6 characters of string
Fim = Left(Fim, 6)

'Creating Output String
TextBox2.Value = "select FIM, FTI_UKIRISH_STAMP_INDICATOR from SECURITY where FIM='" + Fim + "'"

End Sub 

[medialint]

select FIM, FTI_UKIRISH_STAMP_INDICATOR from SECURITY where FIM='91PO.L'

Without all the other hoops have you considered:

select FIM, FTI_UKIRISH_STAMP_INDICATOR from SECURITY where FIM Like '91PO.L%'

[IncDist]

Originally Posted by medialint

select FIM, FTI_UKIRISH_STAMP_INDICATOR from SECURITY where FIM='91PO.L'

Without all the other hoops have you considered:

select FIM, FTI_UKIRISH_STAMP_INDICATOR from SECURITY where FIM Like '91PO.L%'

I'm not searching for something that begins with 91PO.L, I am searching for 91PO.L exactly. The issue isn't with Dbm2 / SQL that line is fine.


The point of this app is to output the exact SQL line of text with the various six digit ticker codes (Fim) so it can be pasted in Dbm2 without having to manually create X number of identical statements by hand using copy and paste which is time consuming.

I want to be able to do one copy (as explained above) and have the app remove the other codes correctly for me, put each new ticker code into an SQL statement and then I do a second copy to put the output into Dbm2.

[the-listener]

Originally Posted by IncDist

Hi All

I am trying to write a small app to take some text i paste into a textbox, remove any spaces and every third line, then keep only the first six characters of each line and output them inside some text.

My first hurdle is getting rid of the spaces. Here is the text I would be pasting in below. In the email it comes in it is in a table style format but when copied and pasted it puts the text into the texbox in the same style as below:

91PO.L
XS0687555572
B71CBK0

12GN.L
SE0004058444
B3XQFV1

Now what I want the app to do is:

91PO.LXS0687555572B71CBK0
12GN.LSE0004058444B3XQFV1

Then remove all but the first six characters:
91PO.L
12GN.L

So It can then finally output for me:

select FIM, FTI_UKIRISH_STAMP_INDICATOR from SECURITY where FIM='91PO.L'
select FIM, FTI_UKIRISH_STAMP_INDICATOR from SECURITY where FIM='12GN.L'

The idea being I can paste this into MySQL and get some values returned.

Private Sub CommandButton1_Click()
Dim Fim As String

'Removes Spaces
Fim = Trim(TextBox1.Value)
Fim = Fim.Replace(Chr(10), Chr(0))
Fim = Fim.Replace(Chr(13), Chr(0))

'Here to show what the above code has actually done
MsgBox Fim

'Taking first 6 characters of string
Fim = Left(Fim, 6)

'Creating Output String
TextBox2.Value = "select FIM, FTI_UKIRISH_STAMP_INDICATOR from SECURITY where FIM='" + Fim + "'"

End Sub 

If the first 6 characters are always present in the form you have shown, why are you messing about with the rest of the text?

simply taking the leftmost 6 characters is enough to generate your code.

code=left$(inputline,6)

is that all you need?

bill stewart - here to listen