Filtering Results for InstrRev

[Guest]

I need to be able to search for specific sub-strings in any position in a
string. For example, in a table of product descriptions, I may need to
search "OREO" where many of the products will have product description fields
that say "NABISCO OREO COOKIES" or "OREO COOKIES" etc. I have been able to
create an InstrRev function that produces results. If "OREO" is null, the
result is 0. If the "OREO" is true, then a number is displayed showing where
in the full string "OREO" is located. My problem is trying to narrow the
results by using criteria. I tried puting the criteria "Not 0" to filter out
all of the false results and an error message displays saying "This
expression is typed incorrectly, or it is too complex to be evaluated. For
example, a numeric expression may contain too many complicated elements. Try
simplifying the expression by assigning parts of the expression to
variables." How do I filter the results to only show the true values?

 

[James]

Hi

Can you not just use a wildcard? i.e. in your criteria searching for
"*OREO*" will provide the matching entries as below

James

 

[Guest]

That does not work unless the entire string is only called "OREO". I need to
search for "OREO" as a sub-string regardless of where it is. I have
successfully created a query using Instr, but that only helps when "OREO" is
the first character, it does not help find "OREO" in strings such as "NABISCO
OREO COOKIES".

 

[Guest]

I apolgozie. Your solution does work. However, I was hoping I could have a
more user-friendly query that prompts a window asking me what criteria to
search. I tried doing something like *[ENTER SEARCH CRITERIA]*, but that
does not work.

 

[John W. Vinson]

I apolgozie. Your solution does work. However, I was hoping I could have a
more user-friendly query that prompts a window asking me what criteria to
search. I tried doing something like *[ENTER SEARCH CRITERIA]*, but that
does not work.

"*" & [Enter search criteria:] & "*"

will work.

John W. Vinson [MVP]

 

[Guest]

Unfortunately, that did not work. No results came up. *OREO* works fine,
but the "*" & [Enter search criteria:] & "*", then typing OREO did not show
anything.

--
G

I apolgozie. Your solution does work. However, I was hoping I could have a
more user-friendly query that prompts a window asking me what criteria to
search. I tried doing something like *[ENTER SEARCH CRITERIA]*, but that
does not work.

"*" & [Enter search criteria:] & "*"

will work.

John W. Vinson [MVP]

 

[John W. Vinson]

Unfortunately, that did not work. No results came up. *OREO* works fine,
but the "*" & [Enter search criteria:] & "*", then typing OREO did not show
anything.

Please post the SQL view of the query.

John W. Vinson [MVP]

 

[Guest]

Here is the SQL for the query with the most reccent solution you suggested:

SELECT [General Items].[UPC No dashes], [General Items].[Upc with Dashes],
[General Items].[WH Item], [General Items].Description, [General Items].Pack,
[General Items].Size, [General Items].[POS Description], [General
Items].Department, [General Items].Measure, [General Items].[Unit Size]
FROM [General Items]
WHERE ((([General Items].Description)="*" & [Enter search criteria:] & "*"));

Here is the SQL for the query in the original problem from my initial post
using the InstrRev function:

SELECT [General Items].[UPC No dashes], [General Items].[Upc with Dashes],
[General Items].[WH Item], [General Items].Description, [General Items].Pack,
[General Items].Size, [General Items].[POS Description], [General
Items].Department, [General Items].Measure, [General Items].[Unit Size],
InStrRev([Description],[Enter description search WITH CAPS],-1,0) AS Expr1
FROM [General Items];

--
G

Unfortunately, that did not work. No results came up. *OREO* works fine,
but the "*" & [Enter search criteria:] & "*", then typing OREO did not show
anything.

Please post the SQL view of the query.

John W. Vinson [MVP]

 

[John W. Vinson]

WHERE ((([General Items].Description)="*" & [Enter search criteria:] & "*"));

Change this to

WHERE ((([General Items].Description) LIKE "*" & [Enter search criteria:] &
"*"));

The = operator treats * as a literal character; LIKE treats it as a wildcard.

John W. Vinson [MVP]

 

[Guest]

There it was. Thank you John, "Like" was huge. Have a good one.
--
G

WHERE ((([General Items].Description)="*" & [Enter search criteria:] & "*"));

Change this to

WHERE ((([General Items].Description) LIKE "*" & [Enter search criteria:] &
"*"));

The = operator treats * as a literal character; LIKE treats it as a wildcard.

John W. Vinson [MVP]