asked on

Need to Summaries Query Resault

I have a app which queries a MS Sql Table which returns me Phone nbr Prefixes,Start and End Nbrs
The field is called prefix ,start,end and has data like

Prefix Start End
===================================
213232 2132320000 2132329999
213233 2132330000 2132339999
213234 2132340000 2132349999
213235 2132350000 2132359999
213238 2132380000 2132389999
213239 2132390000 2132399999
213400 2134000000 2134009999

to save space in my other apps i need to be able to get to be able to find all continues blocks and then summaries them like this

213232_235 2132320000 2132359999
213238_239 2132380000 2132399999
213400 2134000000 2134009999

Any Idea to to tis programaticly ??

ASKER CERTIFIED SOLUTION

JR2003

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

AlexPonnath

ASKER

Thanks for trying but the code does not work, after trying forth and back i found a way to make this work...

Private Sub cmdLergScript_Click()
Dim rs As New ADODB.Recordset ' To run Selcet queries
Dim strSQL As String
Dim s As String
Dim intStart As Double
Dim intEnd As Double
Dim intNbr As Double
Dim intcontinousFlag As Byte
Dim rsCount As Integer

strSQL = Trim(Me.txtSqlQuery)

rs.Open strSQL, cn2

rsCount = rs.RecordCount

Dim oFSO As New FileSystemObject ' to write in error log
Dim oFSTR As Scripting.TextStream ' making stream to access FSO

oFSO.CreateTextFile "errorlog.txt"

Set oFSTR = oFSO.CreateTextFile("C:" & "\lergScript.txt", ForAppending) ' opening the file
For i = 1 To rs.RecordCount

If i <> 1 Then
'This part of the script handles the checking if this is a continues block
If Trim(rs("prefix")) = intEnd + 1 Then
'the block is continues

intEnd = Trim(rs("prefix"))
Else
'the block is not continues
If intEnd <> intStart Then

'we still need to write the prev Range
s = "createRouteList " & "R_" & Trim(intStart) & "_" & Trim(Right(intEnd, 3)) & " " & Trim(intStart) & "0000" & " " & Trim(intEnd) & "9999"
oFSTR.WriteLine s
' This will also Create the Route
s = "addRoute " & "R_" & Trim(intStart) & "_" & Trim(Right(intEnd, 3)) & " " & """" & "2939-Paetec-LAX" & """"
oFSTR.WriteLine s

intEnd = Trim(rs("prefix"))
intStart = Trim(rs("prefix"))

Else

s = "createRouteList " & "R_" & Trim(intStart) & " " & Trim(intStart) & "0000" & " " & Trim(intEnd) & "9999"
oFSTR.WriteLine s

' This will also Create the Route
s = "addRoute " & "R_" & Trim(intStart) & " " & """" & "2939-Paetec-LAX" & """"
oFSTR.WriteLine s

intEnd = Trim(rs("prefix"))
intStart = Trim(rs("prefix"))

End If

End If

Else
' This is the First Record
intStart = Trim(rs("prefix"))
intEnd = Trim(rs("prefix"))

End If

rs.MoveNext
Next i

' This part processes the Last record..
If i = rsCount + 1 Then
If intEnd <> intStart Then

'we still need to write the prev Range
s = "createRouteList " & "R_" & Trim(intStart) & "_" & Trim(Right(intEnd, 3)) & " " & Trim(intStart) & "0000" & " " & Trim(intEnd) & "9999"
oFSTR.WriteLine s
' This will also Create the Route
s = "addRoute " & "R_" & Trim(intStart) & "_" & Trim(Right(intEnd, 3)) & " " & """" & "2939-Paetec-LAX" & """"
oFSTR.WriteLine s

Else

s = "createRouteList " & "R_" & Trim(intStart) & " " & Trim(intStart) & "0000" & " " & Trim(intEnd) & "9999"
oFSTR.WriteLine s

' This will also Create the Route
s = "addRoute " & "R_" & Trim(intStart) & " " & """" & "2939-Paetec-LAX" & """"
oFSTR.WriteLine s

End If
End If
oFSTR.Close ' closing file

End Sub