Transfer first line of seqence to report (1 Viewer)

Magnus1982

Registered User.
Local time
Today, 09:03
Joined
Apr 29, 2017
Messages
41
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
 

June7

AWF VIP
Local time
Today, 08:03
Joined
Mar 9, 2014
Messages
5,423
Why Aaa1 and Aaa6? What criteria determines this? Not clear what you mean by 'number group'.
 

Magnus1982

Registered User.
Local time
Today, 09:03
Joined
Apr 29, 2017
Messages
41
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

Enthusiastic Amateur
Local time
Today, 16:03
Joined
Sep 21, 2011
Messages
14,044
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

Registered User.
Local time
Today, 09:03
Joined
Apr 29, 2017
Messages
41
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

Super Moderator
Staff member
Local time
Today, 16:03
Joined
Sep 12, 2006
Messages
15,613
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

Nifty Access Guy
Staff member
Local time
Today, 16:03
Joined
Jul 9, 2003
Messages
16,244
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

AWF VIP
Local time
Today, 08:03
Joined
Mar 9, 2014
Messages
5,423
Data always starts with 3 alpha characters? If not, should post a true representation of data.
 

Magnus1982

Registered User.
Local time
Today, 09:03
Joined
Apr 29, 2017
Messages
41
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

Registered User.
Local time
Today, 09:03
Joined
Apr 29, 2017
Messages
41
E.q. SN:1234TTT-1234BTY0001
SN:1234TTT-1234BTY0002
SN:1234TTT-1234BTY0003
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 16:03
Joined
Jul 9, 2003
Messages
16,244
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

Super Moderator
Staff member
Local time
Today, 16:03
Joined
Sep 12, 2006
Messages
15,613
I think this sort of logic should output all the sequence heads.
I hope it helps you.

air code
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.
 

Users who are viewing this thread

Top Bottom