Display Missing Numbers - Microsoft Access Forum


	Back to Access Forum Comments List

Display Missing Numbers

Jonah Lloyd

5 years ago

Can't figure this but there must be a way to do it, probably VBA. Anyway I have a list of consecutive user numbers - all 4 figures - for allocating a device to a group of users. So for eg 1000 - 1999 = Police, 2000 - 2999 = Fire Dept etc. As officers come and go the numbers are recycled so say 2022 leaves the service and hands his devices back to me I will delete those devices from the system and recycle them but - in doing so I delete the 2022 as well, there is no easy way out of this. So the question is how to display the numbers not in the sequence that are free to be reallocated. I guess it would have to be a VBA statement to look through the field and where there is a gap ie 2021 then 2023 - display 2022 as missing in the sequence in a query? Does anyone know what I am on about I think I am losing my mind ........ there must be an easy solution?

Adam Schwanz @Reply

5 years ago

Unless I'm missing something you don't need a VBA loop or anything complex.

Why don't you just make a table of the numbers, and then just assign them to the officers out of that table with an "Assigned" yes/no field? Then when they're done you don't need to delete the number, just take it back into the available number stock by marking assigned as false and removing it from the officer.

Then with a query criteria you could only show numbers that are assigned, numbers that aren't assigned, or all of them.

	Scott Axton @Reply 5 years ago
Jonah - There are a TON of videos (free) in the TechHelp Section. Though I do recommend you consider joining as a member. Lots of benefits for the price of a Starbucks (per month!) Check out the Manage Rental Inventory video. Even though it says rental - you could apply this to keeping track of your equipment and it's history.

	Jonah Lloyd OP @Reply 5 years ago
Thanks guys, I will take Adam's advice and use the assigned - not assigned checkbox to do this. I have a lot more experience with Excel and it is sometimes difficult to see the obvious solution in Access 8-)

Richard Rost @Reply

5 years ago

Yes, you will definitely need a loop in VBA to show missing number from a list. I can't think of any non-VBA way to do it. Good news is that it would be a VERY easy loop to program for my students who know Recordsets. A slower method would be using DLookup. You could even use a continuous form with a GoToRecord command and loop until you find a missing value - but that's the cheesy way.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen