Results 1 to 6 of 6
  1. #1
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253

    Parsing a string

    I have a very long string that gets scanned with a barcode scanner. It looks like the following in the code box. All the data up to each * is one record. The number of records varies. There could be up to 30ish records in one string. Should I parse the string at the * for each record first then parse the records or is there a better way? After I parse everything I need to display it in a subform or in a datasheet view for someone to look over the data then write it to the BE data table.



    Code:
    N;1;9999;CD9999R;N;4/29/20;TRUE;89.0;PASS;300.00;-10.00;3.000;75.00;1.00;100.00;25.00;200.00;-20.00;PASS;PASS;11.0;1;JD*N;1;9999;CD9999R;N;4/29/20;TRUE;89.0;PASS;300.00;-10.00;3.000;75.00;1.00;100.00;25.00;200.00;-20.00;PASS;PASS;11.0;1;JD*N;1;9999;CD9999R;N;4/29/20;TRUE;89.0;PASS;300.00;-10.00;3.000;75.00;1.00;100.00;25.00;200.00;-20.00;PASS;PASS;11.0;1;JD
    Thank you for any help.
    --Walker

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I would use 2 arrays.

    First array parse on *.

    Loop that array and parse each element on ; into second array and write data to records.
    Can write to a temp table but I would probably just go into final table and open form to those new records for review.

    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    June

    Could you give me a link to an example for the array part? I have not messed with arrays as of yet. Thank you for your quick response.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    June7 probably meant to direct you to the Split() function to create the arrays:

    https://docs.microsoft.com/en-us/off...split-function
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    Thank you Paul

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Simple example:
    Code:
    Sub ParseData(s As String)
    Dim aryS1 As Variant, aryS2 As Variant, x As Integer, y As Integer
    aryS1 = Split(s, "*")
    For x = 0 To UBound(aryS1)
        aryS2 = Split(aryS1(x), ";")
        For y = 0 To UBound(aryS2)
            Debug.Print aryS2(y)
        Next
    Next
    End Sub
    

    Instead of Debug.Print, code to save array elements to table. Another simple example.
    Code:
    Sub ParseData(s As String) Dim aryS1 As Variant, aryS2 As Variant, x As Integer, y As Integer Dim rs As DAO.Recordset Set rs = CurrentDb.OpenRecordset ("SELECT * FROM tablename WHERE 1=0") aryS1 = Split(s, "*") For x = 0 To UBound(aryS1) aryS2 = Split(aryS1(x), ";") rs.Addnew For y = 0 To UBound(aryS2) rs(y) = aryS2(y) Next rs.Update Next End Sub
    Last edited by June7; 06-23-2020 at 07:02 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 06-07-2013, 09:45 AM
  2. Replies: 2
    Last Post: 03-22-2013, 01:57 PM
  3. Parsing Middle of String
    By OprEowyn in forum Queries
    Replies: 4
    Last Post: 02-20-2013, 10:53 PM
  4. Replies: 6
    Last Post: 02-13-2013, 04:54 AM
  5. Access front end for parsing xml string
    By raghu_nandan1 in forum Programming
    Replies: 0
    Last Post: 04-21-2011, 07:58 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums