Splitting QR code information into different controls (1 Viewer)

Ihk

Member
Local time
Today, 04:09
Joined
Apr 7, 2020
Messages
280
I have question about splitting of QR code into different parts. QRcode/Data matrix contains different information on medical products, information like GTIN, Lot Number, Expiry date, reference. Each company has different format but they follow international standards mentioned below.
1630509614827.png

I will program this for each company differently according to their format. The 1st company which I am starting working, has similar format on all products.
As per international format (with in each company)
  • Number of digits will always remain constant as in pictures which is 48 in total, Similarly for GTIN will be 14, Lot will be 8 and so on
  • GTIN prefix (01), Lot prefix (10), Expiry Date (17), Reference prefix (240)
I have separated / splitted this information as shown by
After update on “code” I applied the following and is is working perfectly fine.
Code:
Me.txtGTN.Value = Mid([txtCode], 3, 14)
Me.txtLot.Value = Mid([txtCode], 19, 8)
Me.txtExpDate.Value = Mid([txtCode], 29, 6)
Me.txtReference.Value = Mid([txtCode], 38, 11)
1630509697059.png

Question:

  • Is this good approach?
  • How Can I split the code on prefix basis (as shown in picture), Please give me sample code here
  • How can I change 6 digits into date format? For example “220630” into date something like 30-06-2022 or 30-June-22 etc
Thanks and waiting for your response.
Here are other products codes

  • 010761333618069310548634011721113024009289313190
  • 010761333618066210548617011721123124009289267190
  • 010401563091901710523701011722103124003183971122
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:09
Joined
Oct 29, 2018
Messages
21,641
Hi. I am not familiar with reading QR codes, but if the individual data segments are always starting in the same position, then using what you showed is probably a good approach. As for converting 220630 into a date, one possible approach is assume all dates are in the 21st century. For example:
Code:
CDate(Format("20" & [DateField],"0000-00-00"))
Otherwise, you could add more checks for the correct century.

Hope that helps...
 

Ihk

Member
Local time
Today, 04:09
Joined
Apr 7, 2020
Messages
280
Hi. I am not familiar with reading QR codes, but if the individual data segments are always starting in the same position, then using what you showed is probably a good approach. As for converting 220630 into a date, one possible approach is assume all dates are in the 21st century. For example:
Code:
CDate(Format("20" & [DateField],"0000-00-00"))
Otherwise, you could add more checks for the correct century.

Hope that helps...
Thank you very much. Yes as far as I have seen the products, individual data segment are starting at the position.
 

isladogs

MVP / VIP
Local time
Today, 03:09
Joined
Jan 14, 2017
Messages
18,309
@Ihk
It sounds like you know more about the structure of QR codes than most of the members here.
Therefore it might be interesting/helpful to forum members if you could upload a demo QR database when you are able to do so

Am I correct in assuming that there are various 'placeholder' values used to separate the actual component data parts?
Also that those values are then discarded - such as the initial 01?
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 22:09
Joined
May 21, 2018
Messages
8,705
I think there may be more to this, that I am missing but why do you need to do the following:
  • How Can I split the code on prefix basis (as shown in picture), Please give me sample code here
If you can split by position, why do you need to split using the prefix?

If I understand correctly the the format is always a 48 digit number consisting of a
(2 digit GTIN prefix) 14 Digit GTIN, (2 digit lot prefix) 8 digit lot (2 digit expiry)6 digit date (3 digit reference prefix) 11 digit reference

The only thing that changes is the prefix for other companies
So I would think your working process would hold for other companies.
 

Ihk

Member
Local time
Today, 04:09
Joined
Apr 7, 2020
Messages
280
@Ihk
It sounds like you know more about the structure of QR codes than most of the members here.
Therefore it might be interesting/helpful to forum members if you could upload a demo QR database when you are able to do so

Am I correct in assuming that there are various 'placeholder' values used to separate the actual component data parts?
Also that those values are then discarded - such as the initial 01?
Yes I will do so, If it will help someone. In international standards especially in medical field QR codes each segment has a fixed place holder.
 

Ihk

Member
Local time
Today, 04:09
Joined
Apr 7, 2020
Messages
280
I think there may be more to this, that I am missing but why do you need to do the following:

If you can split by position, why do you need to split using the prefix?

If I understand correctly the the format is always a 48 digit number consisting of a
(2 digit GTIN prefix) 14 Digit GTIN, (2 digit lot prefix) 8 digit lot (2 digit expiry)6 digit date (3 digit reference prefix) 11 digit reference

The only thing that changes is the prefix for other companies
So I would think your working process would hold for other companies.
Interesting Question. Because as per international standards, these prefixes are fixed to separate each segment. It was my beginning in QR codes, I thought of better getting suggestions and opinions of expert members here. How do they suggest that may be of helpful.
For 6 digits, converting into date. What do you suggest, should I have another control for date format, which will result from after update of control control containing 6 digits.
 

Ihk

Member
Local time
Today, 04:09
Joined
Apr 7, 2020
Messages
280
Hi. I am not familiar with reading QR codes, but if the individual data segments are always starting in the same position, then using what you showed is probably a good approach. As for converting 220630 into a date, one possible approach is assume all dates are in the 21st century. For example:
Code:
CDate(Format("20" & [DateField],"0000-00-00"))
Otherwise, you could add more checks for the correct century.

Hope that helps...
How do you think, this should go for "After update" of a control containing 6 digits , for another control with date format. Which means I should I have two controls on a form. One for these 6 digits (invisible) and another control for real date?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:09
Joined
Oct 29, 2018
Messages
21,641
For 6 digits, converting into date. What do you suggest, should I have another control for date format, which will result from after update of control control containing 6 digits.
Hi. Just curious, did the suggestion I presented earlier not work?
 

Ihk

Member
Local time
Today, 04:09
Joined
Apr 7, 2020
Messages
280
Hi. Just curious, did the suggestion I presented earlier not work?
No, not yet applied. Thats what I asking to place this code. Should it go for after update of (6 digit) control, for another control with date field?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:09
Joined
Oct 29, 2018
Messages
21,641
No, not yet applied. Thats what I asking to place this code. Should it go for after update of (6 digit) control, for another control with date field?
Oh, I see. You would place it wherever you need to have a date value from a text value. Your choice.
 

Ihk

Member
Local time
Today, 04:09
Joined
Apr 7, 2020
Messages
280
Oh, I see. You would place it wherever you need to have a date value from a text value. Your choice.
Actually I tried in the format of that control, it did not change, though date picker is there. But still field is just 6 digits.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:09
Joined
Oct 29, 2018
Messages
21,641
Actually I tried in the format of that control, it did not change, though date picker is there. But still field is just 6 digits.
Unfortunately, the Format property only takes input for formatting the display - not for converting it. The Format() function is used in expressions like in queries or in the Control Source property of a Textbox on a Form.
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 22:09
Joined
May 21, 2018
Messages
8,705
How do you think, this should go for "After update" of a control containing 6 digits , for another control with date format. Which means I should I have two controls on a form. One for these 6 digits (invisible) and another control for real date?
If this works :
Code:
Me.txtExpDate.Value = Mid([txtCode], 29, 6)
And this works
Code:
CDate(Format("20" & [DateField],"0000-00-00"))
Then you can combine into one without the need of two controls
Me.txtExpDate.Value = CDate(Format("20" &Mid([txtCode], 29, 6),"0000-00-00"))
 
  • Love
Reactions: Ihk

CJ_London

Super Moderator
Staff member
Local time
Today, 03:09
Joined
Feb 19, 2013
Messages
16,762
@isladogs -the QR code can return anything, might be a hyperlink or a sentence for example. This is a specific requirement for the OP.

The construction of a QR code is quite complex and to a single international standard. Various place holders and other data have specific locations and the message starts bottom right, goes up then moves to the bottom of the next column to the left and continues up. Rules are applied when one of the predefined placeholders etc are encountered.

I’m working on a QR code generator for access, making some progress but distracted by other projects. But looking promising
 

isladogs

MVP / VIP
Local time
Today, 03:09
Joined
Jan 14, 2017
Messages
18,309
Thanks for filling in some gaps in my knowledge
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 10:09
Joined
May 7, 2009
Messages
19,252
you have a working solution.
only the date is to be resolved.
Code:
Dim dte As Date
Dim sdt As String
Me.txtGTN.Value = Mid$([txtCode], 3, 14)
Me.txtLot.Value = Mid$([txtCode], 19, 8)
sdt = Mid$([txtCode], 29, 6)
dte = DateSerial(Left$(sdt, 2), Mid$(sdt, 3, 2), Right$(sdt, 2))
Me.txtExpDate.Value = dte
Me.txtReference.Value = Mid$([txtCode], 38, 11)
 

Ihk

Member
Local time
Today, 04:09
Joined
Apr 7, 2020
Messages
280
If this works :
Code:
Me.txtExpDate.Value = Mid([txtCode], 29, 6)
And this works
Code:
CDate(Format("20" & [DateField],"0000-00-00"))
Then you can combine into one without the need of two controls
Me.txtExpDate.Value = CDate(Format("20" &Mid([txtCode], 29, 6),"0000-00-00"))
Thank you very much, this works great. Kind regards
 

Ihk

Member
Local time
Today, 04:09
Joined
Apr 7, 2020
Messages
280
you have a working solution.
only the date is to be resolved.
Code:
Dim dte As Date
Dim sdt As String
Me.txtGTN.Value = Mid$([txtCode], 3, 14)
Me.txtLot.Value = Mid$([txtCode], 19, 8)
sdt = Mid$([txtCode], 29, 6)
dte = DateSerial(Left$(sdt, 2), Mid$(sdt, 3, 2), Right$(sdt, 2))
Me.txtExpDate.Value = dte
Me.txtReference.Value = Mid$([txtCode], 38, 11)
@arnelgp , This is perfectly working. Thank you very much. Kind Regards
 
Last edited:

pekajo

Registered User.
Local time
Today, 12:09
Joined
Jul 25, 2011
Messages
133
Hi,
Just a little of the subject are you aware there are a number of different kinds of Qr Codes as it may help to investigate them on Google.
Peter
 

Users who are viewing this thread

Top Bottom