Search a string

[Trogdor]

Hey People,

Is there a method in VBA to search a string for a particular character? I thought there was a command called Instring(), but I must be mistaken. I am trying to see how many vbcrlf's I have in a caption, so that when it reaches 5 I will remove the first line and then add onto the bottom, so that I get a scrolling effect on my labels..


Thanks for any help!

 

[WayneRyan]

T,

You're looking for the InStr function, but ...

There are a lot of combinations:

vbCrLf - Chr(13) & Chr(10)

vbCr - Chr(13)

vbLf - Chr(10)

Are they all VBA generated?

Wayne

 

[wazz]

if you're dealing with labels you could convert them to textboxes with scrollbars.

 

[Trogdor]

instr gives me the position of the vbcrlf, is there a function that gives me the count? Or can anyone think of how to use it to give me a count?

Wazz - I cant use textboxes in this project, they have to be labels.

 

[rat_b76]

You could use a loop, where your instr start number is the position +1 of the previous vbcr.

 

[gemma-the-husky]

if you are using Access after A97 you could use split, and then take the ubound of the resultant array (might be ubound-1)

 

[Trogdor]

gemma, thanks for that Worked a treat After I figured out how ubound worked and how to arrange the code

FUNTASTIC!

 

[raskew]

Hi -

Sounds like the issue is resolved. However, you might want to give this a try:

Function StrCount(ByVal TheStr As String, theItem As Variant) As Integer
'------------------------------------------------------------------
' Purpose:   Counts number of times item occurs in a string.
' Coded by:  raskew
' Arguments: TheStr: The string to be searched.
'            TheItem: The item to search for.
' Returns:   The number of occurences as an integer.
'
' Note: To test:   Type '? StrCount("The quick brown fox jumped over
'                  the lazy dog", "the") in the debug window.
'                  The function returns 2.
'------------------------------------------------------------------
Dim j         As Integer
Dim placehold As Integer
Dim strHold   As String
Dim itemHold  As Variant

    strHold = TheStr
    itemHold = theItem
    j = 0
    
    If InStr(1, strHold, itemHold) > 0 Then
       While InStr(1, strHold, itemHold) > 0
          placehold = InStr(1, strHold, itemHold)
          'Debug.Print placehold
          j = j + 1
          strHold = Mid(strHold, placehold + Len(itemHold))
       Wend
    End If
    StrCount = j
End Function

Best Wishes - Bob

 

[Trogdor]

Okay me again.. Instr() gives me the position of the first math in a string. Is there a method to find the last position in a string?

For example, I am trying to create a button that puts the string wait or wait end in a label (yes it must be a label).

I want to search through the string to see if '"Wait" & vbcrlf' has been pressed and if so I want to write '"Wait End" & vbcrlf' otherwise I write '"Wait" & vbcrlf'. That way I only have one 'smart' button rather then two buttons.

This is what I have been trying, unsuccesfully.

    Dim rst As DAO.Recordset
    Dim lbl As label
    Dim squareID As Long
    Dim albActionLabel As clsActionLabel
    Dim caption As String
    
    Dim Countwait As Integer
    Dim CountEndwait As Integer
    
    squareID = GetValueID
    
    caption = GetCaption(squareID)
    
    ID = GetID(squareID)
    
    If squareID > 0 Then
        Set rst = CurrentDb.OpenRecordset("tblLabels", dbOpenDynaset)
        Set albActionLabel = New clsActionLabel
        Set lbl = Forms![frmbaggage]("lblTest" & Trim(Str(squareID)))
        Set albActionLabel.label = lbl
    
        Countwait = InStr(1, lbl.caption, "Wait" & vbCrLf)
        
        CountEndwait = InStr(1, lbl.caption, "WaitEnd" & vbCrLf)

        If lbl.caption = "" Then
            lbl.caption = caption & "Wait"
        Else
            If Countwait > CountEndwait Then
                lbl.caption = caption & vbCrLf & "Wait"
            Else
                lbl.caption = caption & vbCrLf & "Wait End"
            End If
        End If
        
        Dim count As Integer
        count = UBound(Split(lbl.caption, vbCrLf))
        If count > 4 Then
            lbl.caption = right(lbl.caption, Len(lbl.caption) - InStr(lbl.caption, vbCrLf) - 1)
        End If
    
        rst.FindFirst "LID = " & squareID
    
        rst.Edit
        
        With albActionLabel.label
            rst!LCaption = lbl.caption
        End With
        
        rst.Update
        rst.Close
        Set rst = Nothing
    Else: End If

Any ideas?

 

[WayneRyan]

T,

InStrRev is the function.

If it isn't native, there are examples of replacements for earlier versions.

Wayne

 

[Trogdor]

Thanks Wayne! Worked a treat! It helps when you know all the small little functions that VBA uses