get a list of skipped numbers

[lala]

i need to be able to create a list of all numbers skipped in a range of numbers


so for example from 10000 to 11000, the range will be different each time.
i found an example on the net that tells me where there's a gap but a now need a way to generate all the numbers in the gap.

i don't really like what i have so far, so if someone has a nice way of doing it from start to the end - that'd be perfect. you guys usually write beautiful "couple of liners" for the most complex stuff

thank you

 

[boblarson]

How do you want the result returned? In a table, in an Excel file, what?

 

[lala]

a table would be perfect

 

[vbaInet]

Have a play with the Partition() function. I think that's what it's called. This would need to be run in a query, that is if I'm understanding you correctly.

If you just want to generate a range of numbers between a start and end, then you can use a loop and Step.

 

[lala]

Maybe i'm misunderstanding how to use it but it's not working for me.

In the meanwhile i got everything to work some very ugly and shaky way so i'd like to just have it redone. What can i use to identify missing numbers in a range and generate a list of them?

 

[stopher]

If you had a table with the numbers 10000 to 11000 handy then you could just left join this with your table and just filter on nulls.

However, a standalone solution without the need for a table of numbers is this VBA:

Dim rs As Recordset
Dim a(10000 To 11000) As Boolean

Set rs = CurrentDb.OpenRecordset("tblSource")
'mark the existing records in an array
Do While Not rs.EOF
    a(rs!ID) = True
    rs.MoveNext
Loop

Set rs = CurrentDb.OpenRecordset("tblMissing")
'loop through the array and write unmarked (false) elements to our output table
For i = LBound(a) To UBound(a)
    If a(i) <> True Then
        rs.AddNew
        rs!ID = i
        rs.Update
    End If
Next i

Set rs = Nothing

hth
Chris

 

[gemma-the-husky]

in case you missed the significance of stopher's example - there isn't an intrinsic instant way to do this, although any method will be quick.

either you have to test each value in the range, to see if it is there or not, which is stopher's example above

alternatively, you need a pre-filled table with all the numbers in -and then you can do a find unmatched query to see which ones are missing - but to do that, you have to create the table first, which is a similar problem anyway (again, as stopher pointed out)

 

[lala]

in case you missed the significance of stopher's example - there isn't an intrinsic instant way to do this, although any method will be quick.

either you have to test each value in the range, to see if it is there or not, which is stopher's example above

alternatively, you need a pre-filled table with all the numbers in -and then you can do a find unmatched query to see which ones are missing - but to do that, you have to create the table first, which is a similar problem anyway (again, as stopher pointed out)

i just woke up and about to test it out)))) i thought about creating the table but it just seems so unesthetic))))) if i couldn't find anything else i would))))

but i see what you're saying that there's no easy way to do it but to test the numbers one by one. about to study the code to understand how it does that


stopher, thank you so much!!!! i'm sure it's going to work but i will check back anyway

 

[lala]

everything is perfect except one thing, the numbers i gave you will always change (10000 to 11000) and your code needs a constant. i don't understand why and because of it cant fix it)))))
but once i put the numbers in - works perfect of course
again, thank you and this is not a big deal, only posting in case it's an easy fix

 

[lala]

i think i understand why, will try to fix and post if it works

 

[lala]

Public Function tst2345()
          
DoCmd.RunSQL "DELETE * FROM mtSkippedFileNumbers"

    Dim FirstNo As Integer
    Dim LastNo As Integer
     
FirstNo = DLookup("First", "QFileNumbersStartEnd")
LastNo = DLookup("Last", "QFileNumbersStartEnd")
     


Dim rs As Recordset
Dim a() As Boolean
ReDim a(FirstNo To LastNo) As Boolean

Set rs = CurrentDb.OpenRecordset("QFileNumbers")
'mark the existing records in an array
Do While Not rs.EOF
    a(rs!fileno) = True
    rs.MoveNext
Loop

Set rs = CurrentDb.OpenRecordset("mtSkippedFileNumbers")
'loop through the array and write unmarked (false) elements to our output table
For i = LBound(a) To UBound(a)
    If a(i) <> True Then
        rs.AddNew
        rs!FIleNumber = i
        rs.Update
    End If
Next i

Set rs = Nothing

End Function

google rules. i don't understand why changing

Dim a(10000 To 11000) As Boolean

to

Dim a() As Boolean
ReDim a(FirstNo To LastNo) As Boolean

works but it does

if anyone has a minute to explain - i'd appreciate it, i tried looking it up but probably not googling the right things

 

[stopher]

If you look at the Help on Dim statement:

"You can also use the Dim statement with empty parentheses to declare a dynamic array. After declaring a dynamic array, use the ReDim statement within a procedure to define the number of dimensions and elements in the array. If you try to redeclare a dimension for an array variable whose size was explicitly specified in a Private, Public, or Dim statement, an error occurs."

I don't think that directly answers your question but I think it implies that you can't simply Dim a dynamic array with a variable (only with empty parenthesis), and that you must use ReDim to do the dynamics.

So well done for sorting it out. glad to hear it's all working.

Chris

 

[lala]

If you look at the Help on Dim statement:

"You can also use the Dim statement with empty parentheses to declare a dynamic array. After declaring a dynamic array, use the ReDim statement within a procedure to define the number of dimensions and elements in the array. If you try to redeclare a dimension for an array variable whose size was explicitly specified in a Private, Public, or Dim statement, an error occurs."

I don't think that directly answers your question but I think it implies that you can't simply Dim a dynamic array with a variable (only with empty parenthesis), and that you must use ReDim to do the dynamics.

So well done for sorting it out. glad to hear it's all working.

Chris

didn't think to look at the Dim)))) but i understand now, it really is that simple once it's explained. thank you so much for the code!!