Compressed seq data, again. I want to pay to make it work.

[KC]

OK, per John Spencer advice

SELECT Trades.timetext AS T, Mid([t],4,2) AS M, Mid([t],7,2) AS S,
IIf(>0,[m]+1,[m]) AS BM, -Int(-[bm]/15)*15 AS B
FROM Trades;

Hi Tim

Which ADO reference are you using please?
I cannot get the sub to work.

I am using this sql
SELECT Trades.datetraded, Trades.timetext, Left([timetext],2) AS [hour],
Mid([timetext],4,2) AS [minute], Mid([timetext],7,2) AS sec,
IIf([sec]>0,[minute]+1,[minute]) AS BigMin
FROM Trades;

Is there a function in sql for "ceiling" as equivalent in Excel please?

I tried this approach on a *small* file of data, and it *seemed* to work.
You can see if you can work out the details and whether it works on your
full dataset.
(or if you want to send a sample file I can try it myself out of
interest...)

Right now it dumps the results to a worksheet but an ADO recordset also
has a "GetString()" method.

http://www.w3schools.com/ado/met_rs_getstring.asp

Tim


'**************************************************
Sub TestText()

Dim FileTitle As String, sConnectionString As String
Dim rs As ADODB.Recordset
Dim f, i As Integer, sSQL As String
Dim rngDest As Range

FileTitle = "trades.txt"

sSQL = " select [date], round([time]*24*4)/(24*4) as rndTime, " & _
" max([price]) as MaxPrice, " & _
" min([price]) as MinPrice, " & _
" sum([volume]) as totalVolume from [" & _
FileTitle & "] group by [date], round([time]*24*4)/(24*4) "

sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.Path & ";" & _
"Extended Properties=Text"

Set rs = New ADODB.Recordset
rs.Open sSQL, sConnectionString

Set rngDest = Sheet1.Range("A1")

i = 0
For Each f In rs.Fields
rngDest.Offset(0, i).Value = f.Name
i = i + 1
Next f

rngDest.Offset(1, 0).CopyFromRecordset rs


End Sub
'*************************************************

Thank you for your suggestion. I have asked but no.
"KC" <[email protected]> skrev i meddelandet
My suggestion on Feb 3 is explicit enough.
Give it to your favorite service provider.
I am sure he can code it up accordingly.

"Rolf" <[email protected]> wrote in message