Identifying missing numbers on a sequence

[Luthius]

I would like or a SQL statement or a VBA Function that can identify the 10 missing numbers of a sequence.
For instance
I have tblResults with 15 fields that receive random numbers from 1 to 25. There is no repetition on each sequence of 15 numbers. Only exclusive numbers on the sequence for each record of the table:

tblResults Fields
number1
number2
...
number15

I would like to know the missing numbers of each record of the table.

Example
My First Record Contains the sequence: 1,4,5,6,8,9,11,14,15,16,19,21,22,24,25
The missing numbers for my first record: 2,3,7,10,12,13,17,18,20,23

PS.: Each number of the sequence is allocated on each field of my tblResults. So the function or SQL Statement will check each field to find the missing numbers.
The sequence varies.
Example it can be like: 6,8,9,10,11,12,13,15,16,17,18,19,21,23,25
Missing numbers: 1,2,3,4,5,7,14,20,22,24

 

[Luthius]

The idea is to give to the function parameters. For instance, I need to pick up a number from the dictionary and my function is a numeric function not text.
I changed a little bit but still not working.

Public Function MissingNumbers(strTblName As String, intRecordID As Integer, intItem As Integer)


Dim objDict As Object
Dim objDictTemp As Object


Dim db As DAO.Database
Dim rs As DAO.Recordset


Dim key As Long




Set objDict = CreateObject("scripting.dictionary")
Set objDictTemp = CreateObject("scripting.dictionary")


Set db = CurrentDb
Set rs = db.OpenRecordset(strTblName, dbOpenTable, dbAppendOnly)




key = 0
rs.Index = "PrimaryKey"
rs.Seek "=", intRecordID


'Add all numeric values from the table field
    For i = 2 To 16
        objDictTemp.Add key, rs.Fields(i).Value
        key = key + 1
    Next i



key = 0
'In here the loop needs to check the missing numbers and add to the other dictionary. But it is not working
    For j = 1 To 25
        If Not objDictTemp.Exists(j) Then
            Debug.Print objDictTemp.Exists(j) & j
            objDict.Add key, j
            key = key + 1
        End If
    Next j


MissingNumbers = objDict.Item(intItem)


rs.Close
db.Close




End Function

 

[Gary McMaster]

The "Exists" function only operates on the key. You need to use the numbers coming from rs.Fields(i).Value as the dictionary keys. Then when you loop j= 1 to 25 you will ask "Exists" to determine which of the 25 possible numbers is a key in the dictionary. You don't even use the value portion of the dictionary entry. You do have to pass that parameter but you just set them all the same dummy variable maybe like this:

'rs.Fields(i).Value IS THE KEY

For i = 2 To 16
    objDictTemp.Add rs.Fields(i).Value, "some un-used string value" ' Maybe empty quotes maybe the letter "a"
    'key = key + 1 ' The field value is the key
Next i

'In here the loop needs to check the missing numbers and add to the other dictionary. But it is not working
'Dim sTemp as String
For j = 1 To 25
    If Not objDictTemp.Exists(j) Then
        'Debug.Print objDictTemp.Exists(j) & j
        'objDict.Add key, j ' No more adding to this dictionary. Use a second dictionary if that's what you need to send to MS Access
        'key = key + 1
         sTemp = sTemp & j & "," ' or put these missing numbers in an array to pass back to MS Access
         debug.print sTemp    
    End If
Next j

 

[Luthius]

Thank you very much.
It attends my ideia.
Find below the code with your proposed changes.

Public Function MissingNumbers(strTblName As String, intRecordID As Integer, intItem As Integer, Optional AllNumbers As Boolean)


Dim objDict As Object
Dim objDictTemp As Object


Dim db As DAO.Database
Dim rs As DAO.Recordset


Dim keyValue As Long
Dim strTemp As String
Dim varSpltArray As Variant




Set objDict = CreateObject("scripting.dictionary")
Set objDictTemp = CreateObject("scripting.dictionary")


Set db = CurrentDb
Set rs = db.OpenRecordset(strTblName, dbOpenTable, dbAppendOnly)




keyValue = 0
rs.Index = "PrimaryKey"
rs.Seek "=", intRecordID


    For i = 2 To 16
        objDictTemp.Add rs.Fields(i).Value, keyValue
        keyValue = keyValue + 1
    Next i


    For j = 1 To 25
        If Not objDictTemp.Exists(j) Then
            strTemp = strTemp & j & ","
        End If
    Next j
    
    If AllNumbers = True Then
        MissingNumbers = strTemp
    Else
        varSpltArray = Split(strTemp, ",")
        MissingNumbers = varSpltArray(intItem - 1)
    End If


rs.Close
db.Close


End Function

 

[Luthius]

The bad thing is that I will be attached to the table name in case I use it on a query. Because it doesn't get the table source from the query automatically and is redundant once the function is inside of it.