Transfer first line of seqence to report

[Magnus1982]

Hello Forum,

During my work with access I am struggle with problem I'm In My Knowledge is not Enough. I have guery with specialist number in one column . Let's say
Aaa001
Aaa002
Aaa003
Aaa004

I need to transfer on raport only numbers which are first in number grup. Example:

Aaa001
Aaa002
Aaa003
Aaa006
Aaa007
Aaa008

In this example i need to transfer on raport only

Aaa001 and Aaa001

Can somebody now how to do that

Thank you with advance

 

[Magnus1982]

Sry Aaa1 and Aaa6 need to be transfered

 

[June7]

Why Aaa1 and Aaa6? What criteria determines this? Not clear what you mean by 'number group'.

 

[Magnus1982]

This only example.

Numbers do not in create all the time. Some Times is a gap between them . I need to catch first number from seqence.
For example I am płacing serial number in to data base and the product is splited for bach. First batch is from 1 to 24 and second start from 72 till 138. I need to catch this 1 and 72

 

[Gasman]

Add a field to the DB where the first record of a batch has a value of "HDR", the rest have a value of "DTL" when you add the records.

Then look for those with a value of "HDR"

 

[Magnus1982]

It is not that easy. Imagin you have target 300. You taking scaner and you scaning serial numbers. Serial numbers are mixed then when you will scan 300pcs you dont know how many grups with following numbers was created. Problemy my example was confusing. The idea is that VBA or query will go.trought all number and will cach first numbers from grup. I can not add next column and mark first line becouse during scaning i have mixed numbers

 

[gemma-the-husky]

I think you need a temporary Y/N "Include" field in the table.

Then process the number sequence by iterating a recordset, and mark the ones you want with a "Y". You will process thousands of records very quickly this way.

 

[Uncle Gizmo]

Your first task is to write VBA code to extract the number portion from the text. You need to do this because to get the numbers you want, you are going to have to impose an index on the list to get the order. And the index won't work the right way on text it won't sort in number order, it will sort in text order. Hence you need to extract the number. Once you have extracted the number and got them indexed, I suspect you'll need to examine the numbers individually and look for gaps. You might be able to write code to look at the next number in the list and see if it is Plus One on the current number, however it might be better to look at the number and see if the preceding number is -1 or less. I think I'd have to try it both ways to see which would be the best way.

There was a similar question a few years ago, I provided an example of a record-set loop which is what you're going to need here...

https://access-programmers.co.uk/forums/showthread.php?t=282591


By the way I think Doc Man showed a way of doing it with queries...

 

[June7]

Data always starts with 3 alpha characters? If not, should post a true representation of data.

 

[Magnus1982]

Number is long . It have almost 30 characters . Include leters ,digit, and specialist characters as : . Last four position we talking about there are starting from 0001 and rising up

 

[Magnus1982]

E.q. SN:1234TTT-1234BTY0001
SN:1234TTT-1234BTY0002
SN:1234TTT-1234BTY0003

 

[Uncle Gizmo]

It is not that easy. Imagin you have target 300. You taking scaner and you scaning serial numbers.

Well, it would be easier if you scanned the numbers into individual fields.

See example here:-

https://youtu.be/xsxfsW7jZJk


Sent from my SM-G925F using Tapatalk

 

[Uncle Gizmo]

Well, it would be easier if you scanned the numbers into individual fields.

Sent from my SM-G925F using Tapatalk

I might have got hold of the wrong end of the Stick here, on my phone the bar-code scan you showed appeared to me as a continuous concatenated contiguous block of text. However now I'm online, it looks like you have already got the individual bar codes as separate items. If that's the case then please ignore my last post.

 

[gemma-the-husky]

I think this sort of logic should output all the sequence heads.
I hope it helps you.

air code

dim lastrecord
dim newsequencemarker

lastrecord = ""
sequencemarker= ""

open recordset  (sorted on product code)
while not end of file
     read thisrecord
     if thisrecord<>"" then
         if thisrecord = lastrecord+1  then
              lastrecord=  thisrecord
         else
              'you need to report the first record in this sequence
              [COLOR="Red"]report sequencemarker[/COLOR] 
              sequencemarker = thisrecord
              lastrecord= thisrecord
         end if
     else 'first record
              sequencemarker = thisrecord
              lastrecord= thisrecord
     end if
 loop
'at the end of this loop you will need to test the last item, as it may need reporting.