Newbie: Finding a Range in 60k numbers and concatenating

[talboyd01]

Folks,

I've been using Access for a week and my first project is to query 60,000 telephone numbers and create ranges when there are gaps in the sequence.

For example, TNs 555-123-0000 through 555-123-1000 may be missing a number and so should wind up like this in a new table:
555-123-0000-0100
555-123-0102-1000

It needs to do this until its through with the table. I have a distinct feeling that this goes beyond macros. Can anyone offer any assistance with the VBA that would be needed?

Thanks,
Tal
ttinyterrible@yahoo.com

 

[JerryDennison]

Actually, this can be done with a query (well, a couple of queries anyway). Create a query with the phone number table. Add a calculated field that adds 1 to the phone number. Close and save the query. Create another query using the original phone number table and the query you just created. Join them using the phone number from the phone number table and the calculated field from the query. Double click the join line and change the join type to include all records from the phone number table and only those matching from the query. Drag the phone number from the phone number table to the QBE grid and the calculated field from the query. In the calculated field's criteria put Is Null. You will now have a list of all phone numbers that do not have a number that follows it. No VBA required.

 

[MichaelRed]

Jerry,

Great start. Two issues. First, it doesn't quite get to the output format desired. Second, Phone Numbers are generally stored as strings, so incrementing them is not 'trivial'. I have ALMOST gor this to work, but need to do other things fior a bit:

Here is the "Work in Progress", but it does not deal with incrementing past the punctuation (properly).

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[JerryDennison]

Michael,
You're right about the phone numbers, but I didn't want to scare the guy too much. I figured when he got to that point and needed help figuring out how to increment them we could help. The principles would still be the same, it would only be a matter of specific syntax.

 

[MichaelRed]

O.K. - well first off -I didn't actually post the "(*&^(*&^(**%$&^%$&" code, so here it is:

Public Function basIncrStr(strIn As String) As String
        
    Dim MyChrs() As String
    Dim strTemp As String
    Dim Idx As Integer
    Dim MyChr As String * 1
    Dim MaxChr As String * 1
    Dim CarryChr As String * 1
    Dim blnCryFlg As Boolean

    ReDim MyChrs(Len(strIn))

    Idx = 1
    Do While Idx <= Len(strIn)
        MyChrs(Idx - 1) = Mid(strIn, Idx, 1)
        Idx = Idx + 1
    Loop

    Idx = UBound(MyChrs) - 1
    Do While Idx > 0

        MyChr = MyChrs(Idx)
        Select Case IsNumeric(MyChr)
            Case Is = True              'Numeric thinggy 0 : 9
                MaxChr = &quot;9&quot;
                CarryChr = &quot;0&quot;

            Case Is = False             'Alpha | Punctuation | ...
                MaxChr = &quot;Z&quot;
                CarryChr = &quot;A&quot;
                If (Not (UCase(MyChr) >= &quot;A&quot; And UCase(MyChr) <= &quot;Z&quot;)) Then
                    'OOPs = We have some Punctuation / White space to Deal w/
                    MyChrs(Idx) = MyChr
                    GoTo NxtChr
                End If

        End Select

        If (MyChr < MaxChr) Then
            'Found a char not at limit.  Process
            MyChrs(Idx) = Chr((Asc(MyChrs(Idx)) + 1))
            Exit Do
         Else
            MyChrs(Idx) = CarryChr
            blnCryFlg = True
        End If
NxtChr:
        Idx = Idx - 1
    Loop

    Idx = 0
    Do While Idx < UBound(MyChrs)
        strTemp = strTemp & MyChrs(Idx)
        Idx = Idx + 1
    Loop

    basIncrStr = strTemp
End Function
[/code

But I think it is now O.K. - at least for Phone #'s and MOST trivial Strings.

Second, since you appear to be interested in the incremental soloution - I'll leave the next step to another (YOU) tag-team member.

 MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[talboyd01]

Hey, thanks for the input guys.

Jerry's first response definitely got me farther than I was. Once I got the results, I figured if I subtracted two from every TN except the first one, the result would give me the TNs I needed for the ranges. Then my next thought is to export both columns out to a table and I'll have the From and To columns I was looking for. REALLY clunky, but does this sound right?

I'll definitely try Michael's code too and let you know how it turns out.

Thanks again,

T

 

[JerryDennison]

Do not save this in a table. Use the query for any reports or other activities you'd like to do.

 

[talboyd01]

Well, you're right. The table idea doesn't work.

Now that I have the list of TNs that don't have a TN preceding them, how should I generate the ranges from numbers that do exist?


Thanks again,

Tal