423,850 Members | 1,661 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,850 IT Pros & Developers. It's quick & easy.

How to find missing number from a range

P: 18
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.
Jul 11 '12 #1

✓ answered by Mariostg

Here is an untested loop that gives another potential solution
Expand|Select|Wrap|Line Numbers
  1. Do while not rs.BOF and not rs.EOF //rs is a recordset ordered ASC by ID
  2.     thisID=rs!ID
  3.     rs.movenext
  4.     if thisID + 1<> rs!ID then
  5.         msgbox "Range Broken between " & thisID & " and " & rs!ID
  6.     endif
  7. Loop

Share this Question
Share on Google+
8 Replies


pod
100+
P: 298
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
  1.  
  2. Sub test()
  3. complete_range = Array(1, 2, 3, 4, 5)
  4. incomplete_range = Array(1, 2, 4, 5)
  5. For Each num1 In complete_range
  6.     flag = False
  7.     For Each num2 In incomplete_range
  8.         If num2 = num1 Then
  9.             flag = True
  10.         End If
  11.     Next
  12.     If flag = False Then
  13.         MsgBox num1
  14.     End If
  15. Next
  16.  
  17. End Sub
Jul 11 '12 #2

P: 18
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...
Jul 11 '12 #3

pod
100+
P: 298
pod
OK, then you can still apply the same logic
Expand|Select|Wrap|Line Numbers
  1. Sub sub1()
  2.     Dim rst As ADODB.Recordset
  3.     Call opendb
  4.     Set rst = objConn.Execute("SELECT id FROM Table1")
  5.     'where objConn is my Connection Object
  6.  
  7.     For num = 1 To YourTableMaxID ' ... SELECT MAX(id) FROM YourTable
  8.         flag = False
  9.         Do While Not rst.EOF
  10.             dbID = rst(0)
  11.             If dbID = num Then
  12.                 flag = True
  13.             End If
  14.             rst.MoveNext
  15.         Loop
  16.         If flag = False Then
  17.             MsgBox num
  18.         End If
  19.         rst.MoveFirst
  20.     Next
  21. End Sub
Jul 12 '12 #4

zmbd
Expert Mod 5K+
P: 5,285
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
Jul 12 '12 #5

100+
P: 332
Here is an untested loop that gives another potential solution
Expand|Select|Wrap|Line Numbers
  1. Do while not rs.BOF and not rs.EOF //rs is a recordset ordered ASC by ID
  2.     thisID=rs!ID
  3.     rs.movenext
  4.     if thisID + 1<> rs!ID then
  5.         msgbox "Range Broken between " & thisID & " and " & rs!ID
  6.     endif
  7. Loop
Jul 13 '12 #6

zmbd
Expert Mod 5K+
P: 5,285
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
Jul 13 '12 #7

100+
P: 332
Oh definitely zmbd. We all expect the OP to post his final solution or an explanation for future reference.
Jul 13 '12 #8

NeoPa
Expert Mod 15k+
P: 31,121
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.
Jul 14 '12 #9

Post your reply

Sign in to post your reply or Sign up for a free account.