how to use Instr for more than 2 fields in same table (1 Viewer)

[rehanemis]

Hi,
I am wondering If I could get solution that how to show all rows where keywords are matching in 2 or more columns of a table.
I am using this for one field and working well but don't know how to use for two columns.
Expr1: InStr(1,[CountryName],[Forms]![Main]![txtSearch])

thanks

 

[strive4peace]

hi @rehanemis

perhaps an expression like this:

iif( iif(InStr([CountryName],[Forms]![Main]![txtSearch])>0,1,0)
+ iif(InStr([controlname2],[Forms]![Main]![txtSearch])>0,1,0)
+ iif(InStr([controlname3],[Forms]![Main]![txtSearch])>0,1,0) >=2
, "Matches in at least 2 columns", "doesn't match in at least 2 columns)

is that what you mean?

or is it that you want to search in multiple columns? In that case ...

iif(InStr([CountryName] & [controlname2] & controlname3],[Forms]![Main]![txtSearch])>0,"found", "not found")

(you don't need the first argument of InStr if you are starting from 1 since that's the default)

 

[strive4peace]

to apply a filter, you would use the expression you want for the Filter property of the form and then set FilterOn to be true ... I'm just not sure what you want ~

 

[rehanemis]

hi @rehanemis

perhaps an expression like this:

iif( iif(InStr([CountryName],[Forms]![Main]![txtSearch])>0,1,0)
+ iif(InStr([controlname2],[Forms]![Main]![txtSearch])>0,1,0)
+ iif(InStr([controlname3],[Forms]![Main]![txtSearch])>0,1,0) >=2
, "Matches in at least 2 columns", "doesn't match in at least 2 columns)

is that what you mean?

or is it that you want to search in multiple columns? In that case ...

iif(InStr([CountryName] & [controlname2] & controlname3],[Forms]![Main]![txtSearch])>0,"found", "not found")

(you don't need the first argument of InStr if you are starting from 1 since that's the default)

Thanks Man, I changed code accordingly and it works
Expr1: InStr([CountryName] & [CountryCode2],[Forms]![Main]![txtSearch])

 

[strive4peace]

you're welcome, @rehanemis

now the next step is to call it something other than "Expr1" ... what is before the colon : is the calculated fieldname

 

[arnelgp]

am wondering If I could get solution that how to show all rows where keywords are matching in 2 or more columns of a table.

Did it really satisfy your own condition? Must be in 2 or more column and not "either" column.

 

[strive4peace]

it was not well-explained, but that's ok, @rehanemis! What was asked didn't make sense to me, which is why I offered another solution and it turned out that is really what you meant

 

[rehanemis]

Did it really satisfy your own condition? Must be in 2 or more column and not "either" column.

Yes, CountryName and CountryCode2 are the table fields of same table.
Expr1: InStr([CountryName] & [CountryCode2],[Forms]![Main]![txtSearch])