Wild Card within a Parameter Search

[Kim296]

I have read many post concerning using a wildcard "*" in a parameter search, but haven't found anything that pertains directly to my question.If anyone can help; I would greatly apprecitate it.

I have several search parameters set up in my report. The section where I want to use my wildcard is in the notes field. For example:

I may be looking for a black car, but everyone doesn't enter their notes the same. Some people may put "blk car" and some people may put "black car". Instead of having to search both ways, I would like to be able to enter something like "bl* car" in my search field, so that it will pull back results that would pertain to both entry methods. It may also pull back blue car (which is okay) for my type searches.

Does anyone know how I would formulate this to work properly. The search formula below is what I am currently using (which works fine- with the exception of wild cards).

IF (INSTR (UPPERCASE({@NOTES}), UPPERCASE({?SEARCH1}))>0) AND (INSTR (UPPERCASE({@NOTES}), UPPERCASE({?SEARCH2}))>0) THEN TRUE ELSE IF
(INSTR (UPPERCASE({@NOTES}), UPPERCASE({?SEARCH3}))>0) AND (INSTR (UPPERCASE({@NOTES}), UPPERCASE({?SEARCH4}))>0) THEN TRUE ELSE IF
(INSTR (UPPERCASE({@NOTES}), UPPERCASE({?SEARCH5}))>0) AND (INSTR (UPPERCASE({@NOTES}), UPPERCASE({?SEARCH6}))>0) THEN TRUE ELSE IF
(INSTR (UPPERCASE({@NOTES}), UPPERCASE({?SEARCH7}))>0) AND (INSTR (UPPERCASE({@NOTES}), UPPERCASE({?SEARCH8}))>0) THEN TRUE ELSE IF
(INSTR (UPPERCASE({@NOTES}), UPPERCASE({?SEARCH9}))>0) AND (INSTR (UPPERCASE({@NOTES}), UPPERCASE({?SEARCH10}))>0) THEN TRUE ELSE FALSE

 

[pmax9999]

Try the "Like" function:

If      {@Notes} like {?Search1}
Then    True
Else    False

Cheers
Pete

 

[Kim296]

Thank you Pete,

What you suggested works similar to my current formula, but isn't what I'm looking for in order to use a wildcard. Thanks for you suggestion. Kim

 

[pmax9999]

But it will allow you to use a wildcard! Maybe you just need to explain what you want more clearly.

 

[Kim296]

Pete,
When I tried your formula, it worked when I used a single parameter search (no errors showing), but I couldn't use the formula like I have it with two search parameters. When I tried using in that manner, it gave me formula errors. I probably don't know enough about formulating multiple parameter searches in the format that you have listed.

I tried yours as follows:

If {@Notes} like {?Search1} and {?SEARCH2} THEN TRUE ELSE
If {@Notes} like {?Search3} and {?SEARCH4} THEN TRUE ELSE
If {@Notes} like {?Search5} and {?SEARCH6} THEN TRUE ELSE
If {@Notes} like {?Search7} and {?SEARCH8} THEN TRUE ELSE
If {@Notes} like {?Search9} and {?SEARCH10} THEN TRUE ELSE False

It says error "A boolean is required here" and highlights {?SEARCH2}. How do I overcome this error and make it work?

 

[SkipVought]

If {@Notes} like {?Search1} and [highlight]@Notes} like[/highlight] {?SEARCH2} THEN TRUE ELSE


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Kim296]

Okay Skip, I will try it that way. When I get this figured out and it is error free. Can I just use the "*" wildcard in my search words or is there another step that I would need to do?

Thank you Skip and Pete both for your help.

 

[pmax9999]

I am a little confused by the code from your original post, which I assume is your Record selection formula. Base on that code, it would return those records where it matched the 1st and 2nd search parameters, OR the 3rd and 4th parameters, OR the 5th and 6th and so on. Is that what you are looking for?

Assuming that is the case, you can re-write the code as suggested by Skip.

For your information and for what it is worth, the record selection could be written as follows (which I find simpler):

({@Notes} like {?Search1} and {@Notes} like {?SEARCH2}) or
({@Notes} like {?Search3} and {@Notes} like {?SEARCH4}) or
({@Notes} like {?Search5} and {@Notes} like {?SEARCH6}) or
({@Notes} like {?Search7} and {@Notes} like {?SEARCH8}) or
({@Notes} like {?Search9} and {@Notes} like {?SEARCH10})

If there is more to the Record Selection formula than just this part, enclose the code above in brackets so as to force the correct order for the "or"s.

Hope this helps

Cheers
Pete