How to find missing number from a range

freedemi

Hi,
I ll try to find a way to find the missing number from range of numbers from a specific field and show this number with msgbox.
Any ideas?

Thanks in advance.

pod

not very clear but here is a sub that follows the gist of what I can get from your request

Expand|Select|Wrap|Line Numbers

  
Sub test()

complete_range = Array(1, 2, 3, 4, 5)

incomplete_range = Array(1, 2, 4, 5)

For Each num1 In complete_range

    flag = False

    For Each num2 In incomplete_range

        If num2 = num1 Then

            flag = True

        End If

    Next

    If flag = False Then

        MsgBox num1

    End If

Next
 
End Sub

freedemi

Thanks Pol for your reply.
My problem is that i have a field in table with name (id) and i want to see if the id field has sequence on it and if it has not to show in msgbox which number is missing from range...

pod

OK, then you can still apply the same logic

Expand|Select|Wrap|Line Numbers

 
Sub sub1()

    Dim rst As ADODB.Recordset

    Call opendb

    Set rst = objConn.Execute("SELECT id FROM Table1")

    'where objConn is my Connection Object
 
    For num = 1 To YourTableMaxID ' ... SELECT MAX(id) FROM YourTable

        flag = False

        Do While Not rst.EOF

            dbID = rst(0)

            If dbID = num Then

                flag = True

            End If

            rst.MoveNext

        Loop

        If flag = False Then

            MsgBox num

        End If

        rst.MoveFirst

    Next

End Sub

zmbd

freedemi

Pod was kind enough to post a code for you; however, I would like to see the code with which you have already tried to solve the issue.

It could be something fairly simple in the logic. I know that when I did this in school, I over thought the solution (typical back then... forgot the "keep it simple..." rule). This was a favorite problem of the CompSci prof at university given to students... just because it is so easy to over think the solution.

I do have a code that will work on a simple table so if you will post your code (please remember to use the [code][/code] tags) I would be willing to compare the two.

thnx
-z

Mariostg

Here is an untested loop that gives another potential solution

Expand|Select|Wrap|Line Numbers

 Do while not rs.BOF and not rs.EOF //rs is a recordset ordered ASC by ID

    thisID=rs!ID

    rs.movenext

    if thisID + 1<> rs!ID then

        msgbox "Range Broken between " & thisID & " and " & rs!ID

    endif

Loop

zmbd

I'd still like to see what Freedemi has tried to solve the problem. As I said, this is a favorite problem given to comp-sci students by the profs at my university. The application for finding breaks in a contiguous series is very useful and something banks will do on statements etc... I have such a code in several of my databases that checks for serialized tamper seals on test containers... missing seal.... potential problems.

I have a nice little code that will give the report as asked for in OP. Once Freedemi's code is posted, I will gladly compare it with mine.

-z

Mariostg

Oh definitely zmbd. We all expect the OP to post his final solution or an explanation for future reference.

NeoPa

Some of us (IE. the site) also expect the OP to provide their working/non-working code in the first post, along with a question that makes better sense.

Code relative to the OP's current understanding is so much more valuable than simple posted solutions - Hence the rules in place to encourage decent questions as well as responsive replies.

This question was borderline. If it hadn't already triggered so many responses I would have deleted it and let the OP post it again properly. Any members or experts can bring such questions to the attention of the mods by reporting them.

How to find missing number from a range

✓ answered by Mariostg

Post your reply

Similar topics

Question stats

	Ask Question
Home Questions Articles Browse Topics Latest Top Members FAQ