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

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

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]

May we see what work you've already done?

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

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.