Checking a field in a table for repeating digits

[cmcb2002]

Hi all,



Does anyone know how to check a field within a record for repeating digits? I have a data file where users were instructed to put in repeating digits if they were unable to see a number, so I have many fields with 0000, 0000000, 11111, 111111, etc. Now I need to flag these so they can be manually reviewed. I don't know if I'm overthinking this, but is there a way to check a field so that I can flag these records?

Thanks in advance!

[Micron]

Can only see a code solution. You would have to know the threshold; i.e. does anything more than one number constitute a valid value, or is it 3? 4? The simplest way to flag them might be to even ignore that and if the count divided by the sum is 1, then it's a match.
Afterthought:
The math thing came to me after the first sentence, in which case query might work for you. I was originally thinking of having to loop over the digits/characters and find repeats.

[JoeM]

I have a data file where users were instructed to put in repeating digits if they were unable to see a number, so I have many fields with 0000, 0000000, 11111, 111111, etc. Now I need to flag these so they can be manually reviewed.

Might be too late, but something to consider for future reference.
To make things easier on yourself, why not tell them to use some specific value, instead of giving them free reign of using any repeating digit sequence that they want?
Then you would only need to look for one particular value, and this task becomes exceedingly easy.

[cmcb2002]

I was thinking of the count divided by the sum equaling 1 as well, but was thinking that 102 would equal 1 as well. This still may be the better option because they could be manually reviewed quickly.

I did consider a code option with looping over the digits as well.

Thanks for the feedback - I think I may go with the query and flag if the count/sum=1.

[cmcb2002]

Might be too late, but something to consider for future reference.
To make things easier on yourself, why not tell them to use some specific value, instead of giving them free reign of using any repeating digit sequence that they want?
Then you would only need to look for one particular value, and this task becomes exceedingly easy.

I wish I could tell them - I'm getting data from a third party and have no control over the values. I honestly don't think they ever thought they would use this field, and now it's the key to the data.

[Micron]

That's a great idea, JoeM.
I spoke too soon - even summing the digits would require parsing them into individual elements, so I'm back to leaning on a code solution as I don't see how a query can do it.

[JoeM]

I wish I could tell them - I'm getting data from a third party and have no control over the values. I honestly don't think they ever thought they would use this field, and now it's the key to the data.

I would probably recommend creating your own Function (User Defined Function), that you could then use in your Query.
But you still need to define what criteria it would need to meet, namely the question Micron asked about the minimum threshhold.
For example, if the entry "1" was in a cell, all the characters are the same (since there is only one character in the cell). Does that meet your threshhold?
Or should we look for at least 2 or 3 characters?
So, what is the minimum number of repeating characters we need to look for?

[cmcb2002]

That's a great idea, JoeM.
I spoke too soon - even summing the digits would require parsing them into individual elements, so I'm back to leaning on a code solution as I don't see how a query can do it.

If I were developing the part that captured that field, I would definitely tell them to use a specific value. I wish the original developer had thought of that; that would make this much easier.

Yes, I've definitely gone back and forth between query and code. I'll see what I can put together. Thanks again!

[JoeM]

Actually, I just came up with a Calculated Field in a Query that should do it, with no code needed.
It simply replaces all the characters in the string that match the first character with nothing, and if the resulting string has no length, flag it.
I also put a minimum length on it of 3, which you can change, i.e.

Test: IIf(Len([Field1])>=3,IIf(Len(Replace([Field1],Left([Field1],1),""))=0,"Flag",""))

[cmcb2002]

I would probably recommend creating your own Function (User Defined Function), that you could then use in your Query.
But you still need to define what criteria it would need to meet, namely the question Micron asked about the minimum threshhold.
For example, if the entry "1" was in a cell, all the characters are the same (since there is only one character in the cell). Does that meet your threshhold?
Or should we look for at least 2 or 3 characters?
So, what is the minimum number of repeating characters we need to look for?

It could be a single digit (so just a 1) or anything more than 1 (so 11, 111, etc). Unfortunately there is zero uniformity in the data - so some used a series of 0, some used a series of 1, some used a series of 2...you get the idea. But they are all either single digit or repeating digits (anything more than 1). I figured I would likely need to code but wanted to make sure there wasn't any function out the already for this. I hadn't seen anything when searching online.

[cmcb2002]

Actually, I just came up with a Calculated Field in a Query that should do it, with no code needed.
It simply replaces all the characters in the string that match the first character with nothing, and if the resulting string has no length, flag it.
I also put a minimum length on it of 3, which you can change, i.e.

Test: IIf(Len([Field1])>=3,IIf(Len(Replace([Field1],Left([Field1],1),""))=0,"Flag",""))

Thanks Joe! I'll take a look.

[JoeM]

It could be a single digit (so just a 1) or anything more than 1 (so 11, 111, etc).

The question is, what do you consider "repeating", for the sake of this calculation?
Would you really consider "1" to be repeating (there is only one digit)?
How about "11"? Is 2 enough characters to characterize it as repeating for your purposes?

Whatever your answer is, you can easily change my solution above to whatever you decide by changing the value in red to the minimum length you want to use to consider an entry to be repeating.

[Micron]

I was thinking of the count divided by the sum equaling 1 as well, but was thinking that 102 would equal 1 as well.

I thought of that but dismissed the notion because I can only go by what you wrote, which is that the digits are all the same.
Anyway, I don't know what the heck I was thinking. Given 22222, which sums to 10, divided by the count (5) will never be 1. Nor would 3333 (12) /4 = the count. I haven't had one cup of coffee yet - that's my excuse and I'm sticking to it.

[JoeM]

I haven't had one cup of coffee yet - that's my excuse and I'm sticking to it.

I've had a 5 Hour Energy and a cup of tea.
Doing good now, waiting for the crash!

[cmcb2002]

The question is, what do you consider "repeating", for the sake of this calculation?
Would you really consider "1" to be repeating (there is only one digit)?
How about "11"? Is 2 enough characters to characterize it as repeating for your purposes?

Whatever your answer is, you can easily change my solution above to whatever you decide by changing the value in red to the minimum length you want to use to consider an entry to be repeating.

No I don’t really consider 1 or 11 repeating but for this data it is. The “good” data is typically at least 5 characters or more, non repeating. It’s easy enough to get the single or even double digits out of there. But you’re right, I can modify what you have given me. Thanks so much for your help.