InStr variable (1 Viewer)

[tjs206]

I am trying to use the InStr variable to search through an ADO recordset.

Problem: I get a type mismatch error within my code when trying to run the ADO in a recordset. Another thing, when I just type in a string, and use the InStr variable, it works.

I'm thinking, maybe you can't use the InStr variable when searching recordsets.

Any help would be appreciated.

 

[simongallop]

what is the code that you are using and where does it fail?

 

[tjs206]

Public Sub GetRidofAsterisk()

Dim rst As New ADODB.Recordset

Dim SearchCharacter As String
Dim MyPosition As Integer

rst.Open "Select Billing_name from LTR_CodeA", CurrentProject.Connection, adOpenDynamic, adLockOptimistic

SearchCharacter = "*"
MyPosition = "1"

Do Until rst.EOF
MyPosition = InStr(rst, SearchCharacter)
Loop
End Sub


It fails on MyPosition = InStr(rst, SearchCharacter). It give me an Error 13 Type mismatch.

 

[simongallop]

Try adding rst.movefirst before the loop and rst.movenext after the MyPosition =

 

[tjs206]

I thought of that too, but it didn't work. Thanks for the idea, though.

Can you think of anything else?

 

[rockies1]

It fails on MyPosition = InStr(rst, SearchCharacter) becasue you are passing the InStr function a recordset (rst) when it wants a String.

Try: MyPosition = InStr(rst![Billing_name], SearchCharacter)

Also, this will give you an endless loop unless you rst.MoveNext after MyPosition = InStr(rst![Billing_name], SearchCharacter)

 

[Pat Hartman]

I wonder why it doesn't fail on -
MyPosition = "1"

You are attempting to store a string in a variable defined as integer.

 

[tjs206]

I finally got it to work. I did use the variable name with the recordset, that was the problem of not finding the position. I then used the Left and Mid variables to find the position and eliminate it, as follows:

Function GetRidofAsterisk()

Dim rst As New ADODB.Recordset

Dim SearchCharacter As String
Dim MyPosition, ThePosition As Integer
Dim FirstPart As String, SecondPart As String, ThirdPart As String

rst.Open "Select Billing_name from LTR_CodeA", CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic

SearchCharacter = Chr(42)

Do Until rst.EOF
With rst!Billing_name

MyPosition = InStr(rst!Billing_name, SearchCharacter)
Debug.Print MyPosition
If MyPosition <> 0 Then
FirstPart = Left(rst!Billing_name, MyPosition - 1)
SecondPart = Mid(rst!Billing_name, MyPosition + 1, Len(rst!Billing_name) - MyPosition + 1)
ThirdPart = FirstPart & SecondPart
Debug.Print ThirdPart
rst!Billing_name = ThirdPart
rst.Update
End If
End With

rst.MoveNext
Loop
End Function

Thanks for all your help!