Splitting a string

[Acropolis]

I have made a system for managing service calls and fieldworks.

Part of this is checking the boards we install/service are working correctly.

When the engineer calls in, we check the board - enter serial number, via ODBC talks to main server DB and pulls back what is listed below, along with ID and date/time which is all displayed in a list box.

9853911264,W-AMR,3,2:320:0:52,MAIN STORE,3.57,0,18,001.004.041,0,0*75

This works fine. In the string above are certain bits of information that need to be checked to ensure they are accurate and the board has been programmed correctly.

What I want to do it, is when the user single clicks on the list box, it parses out the various sections of the above string and enters them into some text fields on the form. With some code these can then be checked to see if they are correct or not and alert the user if they are or not.

The checking part I can do, the part I am struggling with is parsing out the correct parts from the string.

The parts will be the same parts required each time, and the string parts wont vary, just not sure how to go about it.

 

[Roku]

You could try using the VBA Split function to separate the string at some delimiter (such as "," or " "). This gives a string array, the elements of which you can put in fields as needed.

If you need more detail, let us know how you would split the string (delimiter) and how the receiving fields would be identified (name, type).

 

[missinglinq]

...In the string above are certain bits of information that need to be checked to ensure they are accurate...

The fact of the matter is that these 'bits of information' should be stored in individual Fields, not jumbled together like this! If you have an overwhelming need to display them together, you can always concatenate them for this purpose. Concatenation is a lot easier than parsing this thing out, especially as you have dashes, colons, commas, periods and, just for good measure, an asterisk thrown in as concatenate!

A cardinal rule of relational databases is that individual data goes in individual fields.

Linq ;0)>

 

[Acropolis]

The fact of the matter is that these 'bits of information' should be stored in individual Fields, not jumbled together like this! If you have an overwhelming need to display them together, you can always concatenate them for this purpose. Concatenation is a lot easier than parsing this thing out, especially as you have dashes, colons, commas, periods and, just for good measure, an asterisk thrown in as concatenate!

A cardinal rule of relational databases is that individual data goes in individual fields.

Linq ;0)>

I take your point and see what you are saying, but think you have got the wrong end of the stick. That is not concatenated that is the raw header data from the XML soap packet that the board sends on each transmission.

If you need more detail, let us know how you would split the string (delimiter) and how the receiving fields would be identified (name, type).

That would be most helpful and appreciated if you could.

The delimiter is , throughout the string, and would be into named fields as follows:

9853911264,W-AMR,3,2:320:0:52,MAIN STORE,3.57,0,18,001.004.041,0,0*75

SerialNbr,IGNORE,Alert,Usage,Location,Battery,IGNORE,CSQ,Firmware,IGNORE,IGNORE

As you can see not all the fields are needed hence I've called them ignore.

Thanks

 

[Roku]

This should work:

Dim strSplit() As String
strSplit = Split("9853911264,W-AMR,3,2:320:0:52,MAIN STORE,3.57,0,18,001.004.041,0,0*75", ",")
Dim intSplit As Integer
For intSplit = 0 To UBound(strSplit)
  Select Case intSplit
    Case 0
      Me.SerialNbr = strSplit(0)
    Case 2
      Me.Alert = strSplit(2)
    Case 3
      Me.Usage = strSplit(3)
    Case 4
      Me.Location = strSplit(4)
    Case 5
      Me.Battery = strSplit(5)
    Case 7
      Me.CSQ = strSplit(7)
    Case 8
      Me.Firmware = strSplit(8)
    Case Else
      ' Ignore
  End Select
Next

Another way could be:

Dim strField() As String, strSplit() As String
Rem do this part once (say in the Form__Open event - but put strField at the top of the form declarations in this case)
strField = Split("SerialNbr,IGNORE,Alert,Usage,Location,Battery,IGNORE,CSQ,Firmware,IGNORE,IGNORE", ",")
Rem repeat this part for each record
Rem retrieve the data string from your relevant source
strSplit = Split("9853911264,W-AMR,3,2:320:0:52,MAIN STORE,3.57,0,18,001.004.041,0,0*75", ",")
Dim intSplit As Integer
For intSplit = 0 To UBound(strSplit)
  If Not strField(intSplit) = "IGNORE" Then Me.Controls(strField(intSplit)) = strSplit
Next

In both cases, you need textboxes named 'SerialNbr', 'Alert' etc. on your form. Be aware of reserved words if you use this approach, so I suggest using text boxes whose name prefix is 'txb' (e.g. 'txbAlert'). You would need to adjust the code accordingly.

 

[Roku]

Sorry, small error in logic. Last few lines of second method should be

For intSplit = 0 To UBound(strSplit)
  If Not strField(intSplit) = "IGNORE" Then Me.Controls(strField(intSplit)) = strSplit(intSplit)
Next

(added subscript to end of statement)

 

[Acropolis]

Thank you very much, helped me out a lot there. I was having a look round as didn't think anyone would be on here at this time on a Sunday morning, and put together a couple of things I found and got it working, in more or less the same way as the example you provided.. strangely enough about 30 seconds before your post

Private Sub lstHeaders_Click()

    Dim str As String
    Dim var As Variant
    Dim i As Long
       
    str = DLookup("[InBoxData]", "inboxvalues_v2", "[InBoxRecNo] ='" & Me.lstHeaders & " '")
    var = Split(str, ",")
    For i = 0 To UBound(var)
        'Debug.Print i, var(i)
        Me.txtUsage = var(3)
        Me.txtSerial = var(0)
        Me.txtLocation = var(4)
        Me.txtAlert = var(2)
        Me.txtBattery = var(5)
        Me.txtCSQ = var(7)
    Next i
    
End Sub

 

[Roku]

Glad to be of help, even on a Sunday

I note you already had the 'txt' prefix in hand, so no need for my warning

 

[Acropolis]

Yeah I prefix everything, makes life a lot easier to find when you need it I find, and stops the problem that you highlighted.

 

[Roku]

... and another afterthought

I just noticed your code loop transfers every array element in every iteration. If you remove the for loop, you have what you need in the simplest solution.

 

[Acropolis]

Ok, I'll confess you've lost me there now lol

 

[Roku]

Sorry

In the loop

    For i = 0 To UBound(var)
        'Debug.Print i, var(i)
        Me.txtUsage = var(3)
        Me.txtSerial = var(0)
        Me.txtLocation = var(4)
        Me.txtAlert = var(2)
        Me.txtBattery = var(5)
        Me.txtCSQ = var(7)
    Next I

the six statements are each executed without being modified by the variable (i). So irrespective of the value of i, the same source and destination fields are used on every iteration. If the Debug.Print statement were not commented out, then you would have only that statement which depends on the value of i for its result.

If you just use

        Me.txtUsage = var(3)
        Me.txtSerial = var(0)
        Me.txtLocation = var(4)
        Me.txtAlert = var(2)
        Me.txtBattery = var(5)
        Me.txtCSQ = var(7)

by itself will achieve what you want.

 

[spikepl]

didn't think anyone would be on here at this time on a Sunday morning

There is not such things as "morning" here. The sun does not set on AWF!

 

[highandwild]

I often use delimited strings and have a function that I use to extract certain elements of the string. The function takes three arguments:
1. The string contained the delimited values.
2. The delimiter used in the string.
3. The index number of the delimited value.

This may prove useful in this project and in the future.

Public Function fncSemiGroup(strString, strDelim, intOption)
Dim varArray As Variant
If intOption = 0 Then
fncSemiGroup = ""
ElseIf InStr(1, strString, strDelim) > 0 Then
varArray = Split(strString, strDelim)
If intOption > UBound(varArray) + 1 Then
fncSemiGroup = ""
Else
fncSemiGroup = varArray(intOption - 1)
End If
Else
fncSemiGroup = ""
End If
End Function