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.
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.