Solved vba code for finding skip number (1 Viewer)

voxy

New member
Local time
Tomorrow, 01:41
Joined
Dec 7, 2019
Messages
15
halo, is there any way to find a skip number and then fill new data to the skip number

example:
my data
1
2
3
5
6
8

so if I want to make a new data, it will fill with number 4 which is skip in my database

thanks before
 

voxy

New member
Local time
Tomorrow, 01:41
Joined
Dec 7, 2019
Messages
15
No, it isn't an autonumber
 

vba_php

Forum Troll
Local time
Today, 13:41
Joined
Oct 6, 2019
Messages
2,884
No, it isn't an autonumber
if it's not an autonumber, then how did you get records with id numbers that have skipped records in it like that? if you want an answer to your questions as written, the answer is there's no way you can do that by using the interface objects. It would require code writing. Maybe a complex subquery could tackle it, but I'm doubting it.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 18:41
Joined
Feb 19, 2013
Messages
16,553
is there any way to find a skip number and then fill new data to the skip number
To find skipped numbers is pretty straight forward but you need to clarify what you mean by 'fill in new data'.

At the moment your question is as wide as 'is there a way to get from London to New York?'. Explain exactly what you are trying to do in simple English.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 18:41
Joined
Sep 12, 2006
Messages
15,614
if you fill in gaps you can get dates out of sequence, and similar issues.

in many ways it's better to manage the sequence effectively, and once a record is created, don't delete it, just set a "deleted" flag for the erstwhile "deleted" record
 

voxy

New member
Local time
Tomorrow, 01:41
Joined
Dec 7, 2019
Messages
15
To find skipped numbers is pretty straight forward but you need to clarify what you mean by 'fill in new data'.

At the moment your question is as wide as 'is there a way to get from London to New York?'. Explain exactly what you are trying to do in simple English.

ok,

the example of my question is like this

I have a database such as:

0001, 0002, 0003, 0004, 0005, 0006, 0007 and so on

and then I delete the no 0005

so the result after I delete the 0005, my database should be like this:
0001, 0002, 0003, 0004, 0006, 0007

and if I want to add a new record, I wanted the new record to fill the skip number, which is in this case is 0005

is there anyway to do that?

thanks before and sorry if I was not explaining my question with more detail
:D:D
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:41
Joined
Oct 29, 2018
Messages
21,358
Hi. Yes, you can do this using VBA or use a query with a Tally table.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 18:41
Joined
Feb 19, 2013
Messages
16,553
OK - so we have now determined you want to fly, next question is from which London airport.

as others have said, filling in gaps has its issues if the number is related to other fields or has some meaning. Also numbers with preceding zeros are not numbers they are text and text has different ordering e.g.

01
0111
02


so before going further, clarify exactly what your value range actually looks like - always 3 preceding zero's? range is 0000 to 9999 and always 4 characters? they are actually numbers formated to show the preceding zeros? They can contain alpha characters.

And since filling in missing values is generally considered a bad idea, perhaps explain a bit about what this field is about and why you need to fill in blanks.
 

namliam

The Mailman - AWF VIP
Local time
Today, 19:41
Joined
Aug 11, 2003
Messages
11,696
Appearently this number is a meaningfull number; as such IMHO "creating gaps" by deleting the records is a big NoNo.

Instead mark the records as "deleted" in a STATUS column or some sort.
so that they will disappear from any selections you might make, instead of physicaly deleting them.

That way you have no gaps and can "simply" add to the end any new entries.

Just my 2 cents :)
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 14:41
Joined
Feb 19, 2002
Messages
42,971
There is no upside to filling in the gaps and replacing one ID with a new record can only lead to future confusion.

PS - Leading zeros indicates you are using a string rather than a number. In mathematics, numbers do NOT have leading zeros. Leading zeros are used in formatting for human consumption.

If all you need is a unique identifier, it might be easier to just use an autonumber. If you want a "smart" ID where your numbers reset within a larger grouping or a date range, we can help with that. For example, some people like the number to restart each month so yyyymm-00001 might be appropriate. In this case the last stanza is a generted number but it is formatted with leading zeros to make it fixed length. This works fine as long as you NEVER exceed the fixed length of the sequence. If your business grows dramatically, fixing the length could come back to bite you.
 

Users who are viewing this thread

Top Bottom