instr in a filter (2 Viewers)

[John Sh]

I have a list of names in a field "Collector" that can include the names of others in a team, .e.g. "Jones. K. Smith. W."
I want to filter this field such that it will return all fields that include "Jones" .
I have tried many variations of the code below but .filter always returns "False".
sFilter, in the example above will be "Jones"

.Filter = "[Collector]= " & InStr(.Collector, sFilter) """

if I use this code

.Filter = "[Collector]= '" & InStr(.Collector, sFilter) & "'"

.filter returns "[Collector] = '0'"

 

[GaP42]

InStr returns the position in a string where the other string is found. You are attempting to filter Collector by the position value. I don't think that is what you want.

InStr function (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

 

[CJ_London]

would think you would want

.Filter = "[Collector] like '*" & sFilter & "*'"

 

[John Sh]

InStr returns the position in a string where the other string is found. You are attempting to filter Collector by the position value. I don't think that is what you want.

InStr function (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

I'm not really stupid, it just seems that way sometimes.
John

 

[John Sh]

would think you would want

.Filter = "[Collector] like '*" & sFilter & "*'"

Thanks. Much appreciated.
John

 

[John Sh]

would think you would want

.Filter = "[Collector] like '*" & sFilter & "*'"

I finished up with .Filter = "[Collector] alike '" & sFilter & "%'"
This works as required.
Thanks again for your help.

 

[cheekybuddha]

I finished up with .Filter = "[Collector] alike '" & sFilter & "%'"
This works as required.
Thanks again for your help.

Why did you choose this method versus @CJ_London's suggestion in Post #3? Just curious.

 

[CJ_London]

I would guess the OP is using ADO recordsets. Would have been helpful to know that

 

[cheekybuddha]

I would guess the OP is using ADO recordsets. Would have been helpful to know that

With an ADO recordset there is no need to use ALIKE but, yes, you would need the % wildcard.

My guess is that they have set ANSI compatible SQL in their db, but unsure without clarification. You can still use ALIKE without that 'feature' being set.

 

[John Sh]

With an ADO recordset there is no need to use ALIKE but, yes, you would need the % wildcard.

My guess is that they have set ANSI compatible SQL in their db, but unsure without clarification. You can still use ALIKE without that 'feature' being set.

Correct. I set the ANSI compatible SQL some time ago as it simplifies "?" using SQL strings and doesn't affect stored queries.
Thank you all for you comments.