Searching Memo Field Problem

[TiggerNYC]

Here's a weird one...I am using a QBF and it is functioning well...except for searching a memo field. I have 15 boxes that a user and input different keywords to search.

Heres the problem...say these are the 3 search keywords:
empl
lead
man

It returns all records that match "empl" (11 records)...but does not function like "empl or lead or man"...it seems to function like "empl and lead" or "empl and man" or "empl and lead and man"

If I switch the words around:
lead
empl
man

It returns all records that match "lead" (5 records)...

Here is the code excerpt in SQL (the query is too complicated for Grid)...Main is the tbl...Open1 is the Memo field...frmrpt is the main QBF...frmsubkeyword is one of frmrpt's subform

((Main.OPEN1) Like "*" & IIf([Forms]![frmRpt]![frmsubkeyword].[Form]![F1] Is Null,"*",[Forms]![frmRpt]![frmsubkeyword].[Form]![F1]) & "*" Or
(Main.OPEN1)=(IIf([Forms]![frmRpt]![frmsubkeyword].[Form]![F2] Is Not Null,(Main.OPEN1) Like "*" & [Forms]![frmRpt]![frmsubkeyword].[Form]![F2] & "*")) Or
(Main.OPEN1)=(IIf([Forms]![frmRpt]![frmsubkeyword].[Form]![F3] Is Not Null,(Main.OPEN1) Like "*" & [Forms]![frmRpt]![frmsubkeyword].[Form]![F3] & "*")) Or
(Main.OPEN1)=(IIf([Forms]![frmRpt]![frmsubkeyword].[Form]![F4] Is Not Null,(Main.OPEN1) Like "*" & [Forms]![frmRpt]![frmsubkeyword].[Form]![F4] & "*")) Or
(Main.OPEN1)=(IIf([Forms]![frmRpt]![frmsubkeyword].[Form]![F5] Is Not Null,(Main.OPEN1) Like "*" & [Forms]![frmRpt]![frmsubkeyword].[Form]![F5] & "*")) Or
(Main.OPEN1)=(IIf([Forms]![frmRpt]![frmsubkeyword].[Form]![F6] Is Not Null,(Main.OPEN1) Like "*" & [Forms]![frmRpt]![frmsubkeyword].[Form]![F6] & "*")) Or
(Main.OPEN1)=(IIf([Forms]![frmRpt]![frmsubkeyword].[Form]![F7] Is Not Null,(Main.OPEN1) Like "*" & [Forms]![frmRpt]![frmsubkeyword].[Form]![F7] & "*")) Or
(Main.OPEN1)=(IIf([Forms]![frmRpt]![frmsubkeyword].[Form]![F8] Is Not Null,(Main.OPEN1) Like "*" & [Forms]![frmRpt]![frmsubkeyword].[Form]![F8] & "*")) Or
(Main.OPEN1)=(IIf([Forms]![frmRpt]![frmsubkeyword].[Form]![F9] Is Not Null,(Main.OPEN1) Like "*" & [Forms]![frmRpt]![frmsubkeyword].[Form]![F9] & "*")) Or
(Main.OPEN1)=(IIf([Forms]![frmRpt]![frmsubkeyword].[Form]![F10] Is Not Null,(Main.OPEN1) Like "*" & [Forms]![frmRpt]![frmsubkeyword].[Form]![F10] & "*")) Or
(Main.OPEN1)=(IIf([Forms]![frmRpt]![frmsubkeyword].[Form]![F11] Is Not Null,(Main.OPEN1) Like "*" & [Forms]![frmRpt]![frmsubkeyword].[Form]![F11] & "*")) Or
(Main.OPEN1)=(IIf([Forms]![frmRpt]![frmsubkeyword].[Form]![F12] Is Not Null,(Main.OPEN1) Like "*" & [Forms]![frmRpt]![frmsubkeyword].[Form]![F12] & "*")) Or
(Main.OPEN1)=(IIf([Forms]![frmRpt]![frmsubkeyword].[Form]![F13] Is Not Null,(Main.OPEN1) Like "*" & [Forms]![frmRpt]![frmsubkeyword].[Form]![F13] & "*")) Or
(Main.OPEN1)=(IIf([Forms]![frmRpt]![frmsubkeyword].[Form]![F14] Is Not Null,(Main.OPEN1) Like "*" & [Forms]![frmRpt]![frmsubkeyword].[Form]![F14] & "*")) Or
(Main.OPEN1)=(IIf([Forms]![frmRpt]![frmsubkeyword].[Form]![F15] Is Not Null,(Main.OPEN1) Like "*" & [Forms]![frmRpt]![frmsubkeyword].[Form]![F15] & "*")))

What am I missing?

Thanks.

 

[Jack Cowley]

I would suggest that you look at this article and use it instead of your current approach. Using this approach you can use Or for that particular field you want to search.

hth,
Jack

 

[TiggerNYC]

Thanks for the reply...I originally tried it...but gave up (I am newer to utilizing a lot of VBA) because the QBF has 11 subforms and a lot of fields...this seemed to work for everything but searching the memo field...

If you have any suggestions which would utilize my current approach, I would appreciate it.

Thanks.

 

[dcx693]

I started looking through your code and thought "holy...". Doesn't it seem like a huge unmanageable amount of code for something which should be conceptually as simple as:
WHERE Main.Open1="empl" or Main.Open1="lead" or Main.Open1="man" ??

The solution that Jack Cowley posted is perhaps conceptually daunting for you at first, but believe me, in the long run you will be glad if you take the time to learn it. It's an extremely powerful and useful technique for creating dynamic SQL queries. I use the technique in almost all my search forms and it vastly simplifies the coding.

 

[Jack Cowley]

This article may give you some ideas on how to search for multiple criteria. As dcx693 suggested, I would take the time to learn the QBF method I suggested earlier.

Good luck with your project!

Jack

 

[TiggerNYC]

Thank you for all of your help. I will be working on the VBA version of my QBF...

but I figured out my quick fix... I eliminated the (main.open1)= from the code and it worked perfectly...

((Main.OPEN1) Like "*" & IIf([Forms]![frmRpt]![frmsubkeyword].[Form]![F1] Is Null,"*",[Forms]![frmRpt]![frmsubkeyword].[Form]![F1]) & "*" Or
IIf([Forms]![frmRpt]![frmsubkeyword].[Form]![F2] Is Not Null,(Main.OPEN1) Like "*" & [Forms]![frmRpt]![frmsubkeyword].[Form]![F2] & "*")

 

[Jack Cowley]

Glad to hear you have it working. Continued success...

Jack