Help separating some data

[NeoPa]

it could oocur by chance that there is a space in the 11th character of the short code data.

I rather assumed that may be possible, hence the InStr() instead of the simpler Mid() function usage.

[NeoPa]

Just out of curiosity, I ran some simple Benchmark Tests against the 2-Phase SQL code that NeoPa posted in Post #11. NeoPa's code processed 127,000 Records and populated the appropriate Tables with the relevant Values, either 10-Digit prefix or not, in an Average of 3.474 seconds over Multiple Trials. I thought the results were pretty impressive. Nice job NeoPa.

Thanks for the compliment ADezii.

I found very early on in my work with Access (when I tried to do it another way) that using SQL to execute any changes was orders of magnitude faster than trying to process the data manually in the VBA code.

[Dan2kx]

I rather assumed that may be possible, hence the InStr() instead of the simpler Mid() function usage.

not sure i follow? that would organise the data incorrectly?

[NeoPa]

not sure i follow? that would organise the data incorrectly?

I assume you are unsure why the Mid() function call would not work correctly.

It would fail in that the following data would be treated as a ten character start string, when in reality it should not be :

442092 - B D BACTEC PLUS AEROBIC/F MEDIUM VIAL - BOX OF 50

The code using InStr() works correctly of course.

[Dan2kx]

Sorry my turn for the head smacking

Thanks for the help

[NeoPa]

Be gentle with yourself. A damaged head will only cause you more confusion :D