missing sequence numbers (1 Viewer)

I have a table (A) that basically holds invoices. It has an invoice number which the user wants to be sequential. Another table (B) holds the last used sequence number so that. When a new invoice is created the sequence number from Table B is read, 1 added to it and that is used for the new invoice. Table B is then updated with the new sequence number.

Sometimes invoices are deleted creating "gaps" in the invoice sequences.
It is a requirement to reuse this where possible (there are circumstances when they can't be reused which isn't important at this juncture).

I've found code on the internet which I can create a query to find the missing gaps. This code returns Missing From to Missing to

The code from the internet is as below. I've had to change it to use the right table names and allow a filter to be applied as wellut basically MyTable is the invoice Table A and MyNumber is the sequence number.

SELECT ([MyNumber]+1) AS MissingFrom, DMin("MyNumber","MyTable","MyNumber>" & [MyNumber]) AS MissingUntil

FROM MyTable

WHERE (((DMin("MyNumber","MyTable","MyNumber>" & [MyNumber]))<>([MyNumber]+1)));

This code works! So If my number is
1
4
5
6
9
10
11
The query returns
2 4
7 9

I then have code that when a new invoice is created the user has the option to pick sequence 2,3,7,8. If they do not pick a number the sequence number from Table B is used.

This all works fine except for one small thing. If the sequence number on table B is not 9 then there are gaps after 9. Let's say the sequence number on table B is 14 then I want the query above to return
2 4
7 9
12 15

I can't figure out how to do this.Any ideas?

I would UNION the next number into a query with all the used numbers and then run your missing digit query off that:

qrrNumberGaps_sub1:

Then reconfigure the query you posted to use qryNumberGaps_sub1 instead of MyTable.

plog said:

I would UNION the next number into a query with all the used numbers and then run your missing digit query off that:

qrrNumberGaps_sub1:

Code:Copy to clipboard

SELECT MyNumber FROM MyTable
UNION ALL
SELECT (LastSequence +1) AS MyNumber FROM TableB

Then reconfigure the query you posted to use qryNumberGaps_sub1 instead of MyTable.

Click to expand...

OK I'll give it a whirl!

Works a treat, thanks.
I was on the right track thinking I needed a UNION but was looking at doing something with the second query!!