Using InStr or Like in a query

[MCTampa]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Resort Codes
[/TD]
[TD]Resort Searches
[/TD]
[/TR]
[TR]
[TD]MM1
[/TD]
[TD]HNS;HSL;MG1;MG3;MG5;MGA;MM1;WNL
[/TD]
[/TR]
[TR]
[TD]GTR
[/TD]
[TD]HNS;GTR;MG1;MG3;MG5;MGA;MML;WNL
[/TD]
[/TR]
[TR]
[TD]NCV
[/TD]
[TD]HNS;HSL;MG1;MG3;MG5;MGA;MML;WNL
[/TD]
[/TR]
[TR]
[TD]MKO
[/TD]
[TD]HNS;HSL;MG1;MG3;MG5;MGA;MKO;WNL
[/TD]
[/TR]
</tbody>[/TABLE]
Hello,

I have a table with two columns: list of resort codes and a list of resort searches.
I would like to create query in which Access evaluates whether or not a resort code is found in the corresponding search list.
I am trying to use the Like method, but it only seems to work when there is just one resort in the search field and it happen to be a match.

Any ideas?

Thanks

 

[Joe4]

Use Query Criteria like this:

Like "*MML*"

 

[MCTampa]

Yes, but then I have to specify the resort code I want to search for each time.
I want access to evaluate each row and only bring back the rows where the resortID is present in the resort search field

I'm trying to use something like

Like [Output Test]![Resort ID] & '*'
or
Like '*[Output Test]![Resort ID]*'

The former only returns matches when there is one resort in the search criteria.

 

[Joe4]

OK. I misunderstood what you were asking, but think I see what you are trying to do now.
Add this field to your Query (which you can elect to not display, if you like):

x: InStr([Resort Searches],[Resort Codes])>0

Then, in the Criteria of this field, add this:

True

I think that will do what you want, which is return all records where the Resort Code from that record exists in the Resort Searches in that same record.

 

[welshgasman]

Construct the search as HNS,HSL etc and the use the IN clause

WHERE ResortCode IN (HNS,HSL,MG1,MG3,MG5,MGA,MM1,WNL)

HTH

 

[MCTampa]

Construct the search as HNS,HSL etc and the use the IN clause

WHERE ResortCode IN (HNS,HSL,MG1,MG3,MG5,MGA,MM1,WNL)

HTH

What I want to write is something like Where [Alert Details]![Resort Codes] In ([Alert Details]![Rental Alert ID]) so I don't have to specify the resort codes I'm searching for. But I cannot get it to work.

 

[Joe4]

What I want to write is something like Where [Alert Details]![Resort Codes] In ([Alert Details]![Rental Alert ID]) so I don't have to specify the resort codes I'm searching for. But I cannot get it to work.

Did you miss my last reply?
I showed you how to do that, once I saw what you were after.