Missing #'s

[ladybug via AccessMonster.com]

I have a table with a field called parent_no, that is set to data type number.
I have loaded data into this table. I need to run a query and tell me what
#'s are not in the table. They are four digit #'s, but I am allowing for 5
(up to 99999).

For example, my table has 0001, 0002, 0003, 0008, 0010, etc.

I want the query to return 0004, 0005, 0006, 0007,0009

Any help is appreciated

 

[Lord Kelvan]

you need to create a table which contains all the numbers from 0005 to
9999 and then use a query to compare against it otherwise you have to
build a loop in VBA to compare it and add the numbers into a array
then display it after. as a note a database query cannot return
something that isnt there less you define it somewhere soem how. what
you can do is compare the gap between the numbers and get a result
like

number1 number2 gap
1 2 1
2 3 1
3 8 5
8 10 2

but because the actual numbers dont exist in your databse you cannot
return them as far as i know.

Regards
Kelvan

 

[KARL DEWEY]

0001, 0002, 0003, 0008, 0010, etc. are not numbers but are text and I did not
try the query on text.
If it will not run then try editing it so that A.MyList becomes
Val(A.MyList).

Substitute your table and field names. It will take a little time to run.
SELECT (SELECT A.MyList + 1 FROM tblListOfNumbers AS A WHERE A.ID = (SELECT
A.ID - 1 FROM tblListOfNumbers As A WHERE A.ID = tblListOfNumbers.ID + 1 AND
A.MyList <> tblListOfNumbers.MyList + 1)) & ' to ' & (SELECT A.MyList - 1
FROM tblListOfNumbers AS A WHERE A.ID = (SELECT A.ID FROM tblListOfNumbers
As A WHERE A.ID = tblListOfNumbers.ID + 1)) AS MissingRange
FROM tblListOfNumbers
WHERE (SELECT A.ID - 1 FROM tblListOfNumbers As A WHERE A.ID =
tblListOfNumbers.ID + 1 AND A.MyList <> tblListOfNumbers.MyList + 1) IS NOT
NULL;