Extract text when it matches specific format

[newbiecoder]

So the approach might be to start from the right and in a loop For i = 1 To Len(str) where str is the value being examined, increment counter n looking for 8 successive integers. If not successful at any point, start n at 0 again and continue. When 8 are found, go left 1 more character and extract a total of 9 IF that character is alpha. ??
If 9th isn't alpha then carry on until 1 alpha is found and extract 9 from there??



EDIT: if that is accurate, you should post a db with a table of test data. It should contain a field for storing the derived value as well.
I don't mind helping out with complex problems, but I believe it's incumbent upon the poster to do some of the work.

I always enjoy your input. Keep on jumping in

I do believe that should work! I am compiling sample data now and will upload very soon.

[newbiecoder]

Test Database.zip

Attached is a sample DB that has a table with two columns (RECORD_KEY and ACC_NO). I prefilled the ACC_NO field so that you could see what the expected result should look like. Any help with this would be extremely helpful.

[ssanfu]

Here is my attempt..... (Micron will probably have a better way )

I renamed "Acc_NO" to "Acc_Num"... I dislike seeing "NO" in a name when it means "Num".
There were a couple of references that were missing (I have A2010), so I had to fix that........
But see if this is close to what you are looking for.



From here (if it meets your needs), the code could be converted to a function so it could be used in a query. Of course, in a query, you would not need/want the update section.


(Edit: dB removed due to technical issues. (OP broke it)

[newbiecoder]

Yes, thank you!! This is exactly what I need. Thank you both very much! This looks to be along the lines of what Micron was suggesting.

[newbiecoder]

I tested the code you provided a little further and found a slight issue. I am not sure why it is happening though. I typed "efegergwrg757" into the RECORD_KEY field and the ACC_NUM field populated with "ergwrg757". Any ideas on how to prevent it from returning false data?

[Micron]

Hmmm. At first I thought I was beaten to the punch. Maybe quicker to fix the solution's "problem" rather than me continue. I have it working for anything up to this length at least
01Z36294756B87654321
plus I was updating the table on the fly rather than relying on a query. Will have to see how much better ssanfu's is but am signing off for the night. Almost 1 AM

[ssanfu]

Now why did you have to go and break it???
It was working soooo well!

But is was a good catch...

Try and break it again... go ahead - just try!



(9 pm here - but I'm tired)