Finding missing receipt number

[ETE]

I like most people that seem to converge on this group am a very new
user to Microsoft Access. I started by trying to create an excel
program to complete was I was trying to accomplish. Because of its
limitations I had to find something else. Well, here I am. I have
built a fairly simple program (I am not a programmer and I don't really
understand most of the code, although I think that I might be slowly
catching on) that monitors our receipts from our local landfill. It
works great for what I want it to do (so far). The thing is, every
receipt number has be accounted for. What can I do to scan the receipt
numbers and find what number is missing from the sequence?

Table: Data
ReceiptNumber is the field

Eric

 

[Jeff Boyce]

Eric

Are the receipt numbers created by Access, or entered by the user? If
entered by Access, is this field an Autonumber datatype? If it is, you will
NOT be able to guarantee sequential receipt numbers ... that is not what
Autonumber promises.

More info, please...

Jeff Boyce
Microsoft Office/Access MVP

 

[ETE]

I understand the concept of autonumbers. The receipt numbers are
printed directly on each individual receipt. Therefore, they are
entered by the user. This ensures that each receipt is easily
trackable.

One other thing. Although duplicates are not supposed to occur, this
just isn't true. Therefore, I set up my table so that the receipt
numbers are a text field (this lets me arbitrarily label duplicate
numbers with an A or B, 0001A and 0001B for instance). It might be
that I need to changes these fields to numbers instead.

What can I do?

 

[Marshall Barton]

I like most people that seem to converge on this group am a very new
user to Microsoft Access. I started by trying to create an excel
program to complete was I was trying to accomplish. Because of its
limitations I had to find something else. Well, here I am. I have
built a fairly simple program (I am not a programmer and I don't really
understand most of the code, although I think that I might be slowly
catching on) that monitors our receipts from our local landfill. It
works great for what I want it to do (so far). The thing is, every
receipt number has be accounted for. What can I do to scan the receipt
numbers and find what number is missing from the sequence?

Table: Data
ReceiptNumber is the field

This query will find the first number in each gap of missing
numbers.

SELECT Data.ReceiptNumber+1 AS GapStart
FROM Data LEFT JOIN Data AS T2
ON Data.ReceiptNumber+1 = T2.ReceiptNumber
WHERE T2.ReceiptNumber Is Null

It gets a lot messier to find all the numbers in the gaps.

 

[ETE]

Excellent. Thank you guys very much.