Splitting QR code information into different controls (1 Viewer)

[Ihk]

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.

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.

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)

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]

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:

CDate(Format("20" & [DateField],"0000-00-00"))

Otherwise, you could add more checks for the correct century.

Hope that helps...

 

[Ihk]

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:

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]

@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]

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]

@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]

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]

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:

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]

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]

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]

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]

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]

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]

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 :

Me.txtExpDate.Value = Mid([txtCode], 29, 6)

And this works

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"))

 

[CJ_London]

@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]

Thanks for filling in some gaps in my knowledge

 

[arnelgp]

you have a working solution.
only the date is to be resolved.

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]

If this works :

Me.txtExpDate.Value = Mid([txtCode], 29, 6)

And this works

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]

you have a working solution.
only the date is to be resolved.

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

 

[pekajo]

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