Solved Split string in 1 textbox into multiple textboxes

[Sniper-BoOyA-]

Hi,

When populating a field using a QR code scanner i get the following string: F-W7F5LA-LENO|0000-000000|B01|2|1.00|2.00|z
However, I would like to split these in multiple textboxes. The delimiter in this case is the "|" character.

Example:

textbox1: F-W7F5LA-LENO
textbox2:0000-000000
textbox3:B01
textbox4:2
textbox5:1.00
textbox6:2.00
textbox7:z

I know i can use the LEFT and RIGHT functions to retrieve characters to the left of the first "|".
Just like inStr in the Query to retrieve location of the first "|", and retrieve some data that way.

However, since there are multiple "|" in the string, i was wondering if there is something similar to Excels Text to Columms function.

Thanks in advance!

Regards,
Michael Schrijver

 

[arnelgp]

me.textbox1=split_text(me.txtQrCode, 1, "|")
me.textbox1=split_text(me.txtQrCode, 2, "|")
me.textbox1=split_text(me.txtQrCode, 3, "|")
me.textbox1=split_text(me.txtQrCode, 4, "|")
me.textbox1=split_text(me.txtQrCode, 5, "|")
me.textbox1=split_text(me.txtQrCode, 6, "|")
me.textbox1=split_text(me.txtQrCode, 7, "|")

In a Standard Module:

Public Function split_text(ByVal s As String, ByVal i As Integer, Optional ByVal delim As String = ",") As String
    ' s     = the string to split
    ' i     = the index number (1 base)
    ' delim = the delimiter string
    '
    On Error GoTo err_handler
    split_text = Split(s, delim)(i - 1)
    Exit Function
err_handler:
    split_text = ""
End Function

 

[Sniper-BoOyA-]

Hi Arnelgp,

Thanks for your reply.
I managed to get it to work somewhat differently, but it seems to do the job just fine.

Here is the solution i came up with:

Dim names() As String
names = Split(Me.qrcode, "|")

Me.boringnr = names(2)
Me.monstrnr = names(3)
Me.dieptevan = names(4)
Me.dieptetot = names(5)