Clarify search with Option group, "Any", "All" or "the

[splats]

Greetings

I have a search that is just not working the way it should. Textbox with an option group and a command button on a form. It works great for "All" and "the exact phrase" options but includes records that do not meet the criteria for "Any" option. Basically when I enter in a couple of words in the textbox and select an option, I would like the search to be based on the option that is selected.

Below is the code. Any ideas would be appreciated.

Thank you

TinaT

Private Sub cmdSearch_Click()
    Dim arrSearch() As String
    Dim i As Integer
    Dim strWhere As String
    'Dim rst As DAO.Recordset
    Dim lngRcnt As Long
    If txtSearchFor <> "" Then
        If fraUsing = 1 Then ' any
            arrSearch() = Split(txtSearchFor, " ", -1, 1)
            For i = 0 To UBound(arrSearch)
                If i = 0 Then
                    strWhere = strWhere & " ("
                Else
                    strWhere = strWhere & " OR "
                End If
                strWhere = strWhere & " ([Abbreviation] & ' ' & [File #] & ' ' & [Title] & ' ' & [Hyper Link] & ' ' & [Consultant] & ' ' & [Month] & ' ' & [Year]& ' ' & [Study TypeID] LIKE '*" & Trim(arrSearch(i)) & "*')"
            Next
        ElseIf fraUsing = 2 Then ' all
            arrSearch = Split(txtSearchFor, " ", -1, 1)
            For i = 0 To UBound(arrSearch)
                If i = 0 Then
                    strWhere = strWhere & " ("
                Else
                    strWhere = strWhere & " AND "
                End If
                strWhere = strWhere & " ([Abbreviation] & ' ' & [File #] & ' ' & [Title] & ' ' & [Hyper Link] & ' ' & [Consultant] & ' ' & [Month] & ' ' & [Year]& ' ' & [Study TypeID] LIKE '*" & Trim(arrSearch(i)) & "*')"
            Next
        Else
            strWhere = strWhere & " ("
            strWhere = strWhere & " ([Abbreviation] & ' ' & [File #] & ' ' & [Title] & ' ' & [Hyper Link] & ' ' & [Consultant] & ' ' & [Month] & ' ' & [Year]& ' ' & [Study TypeID] LIKE '*" & Trim(txtSearchFor) & "*')"
        End If
        strWhere = strWhere & ") "
        [Frm-Subrec2].Form.Filter = strWhere
        [Frm-Subrec2].Form.FilterOn = True
    Else
        [Frm-Subrec2].Form.FilterOn = False
    End If
End Sub

 

[CautionMP]

tinat,
Just a thought, the [tt]IN[/tt] clause may be better suited to what your doing. Should the rest of the filter be connected with an [tt]AND[/tt] instead of an [tt]OF[/tt]?

    If txtSearchFor <> "" Then
        If fraUsing = 1 Then ' any
            arrSearch = Split(txtSearchFor, " ")
            strWhere = [b]"IN ('" & Join(arrSearch, "','") & "') [red]OR[/red]"[/b] & _
            " ([Abbreviation] & ' ' & [File #] & ' ' & [Title] & ' ' & ...

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[Remou]

Have you got an example of an unexpected result? I notice that you do not strip double-spaces, which may lead to LIKE '**'.

 

[splats]

Interesting take Remou. I have not thought of that. I will check to see if double spaces are the cause of the problem. How can I remove them from the results? The results include the data and other data that should not be picked up. Perhaps this other data has double spaces thereby creating part of the problem.

Caution MP i tried your suggestion however, a run time error resulted.

Thank you for your ideas.

TinaT

 

[splats]

Greetings again Remou

I have 25 records that are being recognized that should not be. when I type in "report quality" I get 237 records of which the following 25 are also resulting. I do not know why I am getting these records when I should not be. Any suggestions would be appreciated. I do not see double spaces as being the issue for these.

Thanks

TinaT

Abbreviation	File #	Title	Hyper Link	Consultant	Month	Year	File Type
E	02	Urban Salmon Habitat Program - Nunns Creek Watershed	URBANSALMON
Roth Environment	Jan.	1998	Plan
E	19	Floodplain Mapping Program Campbell & Quinsam Rivers	E19
Klohn Leonoff	May	1989	Map
E	42	Beaver Lodge Forest Lands – Resource Use Plan	BEAVERLODGE
Ministry of Environment	July	1994	Plan
E	43	Restoration Prescriptions and Cost Summary for Nunns Creek	NUNNSCREEK
Komari Wong	Mar.	2002	Plan
E	52	Environmental Action Plan A 5-Year Plan	ENVACTION
Staff	Dec.	2003-2007	Plan
G	18	Preliminary Site Investigation Stage 1, Tyee Spit	G18
Levelton Engineering	July	2001	Study
PA	04	Tyee Spit Long Range Plan	PA4
T. O’Brien	May	2002	Plan
PA	07	Campbell River Estuary Management Plan Update	PA7
Qu’West	Oct.	2002	Plan
RD	10	Dogwood Street Corridor Safety & Traffic Study	DOGWOOD
Delcan Corp	Apr.	2002	Study
RD	37	Master Transportation Plan for Area Roads for the District of Campbell River	MASTERTRANS
Ward Consult.	May	2004	Plan
SA	29	South Campbell River Sewer Study	SEWER
	May	2001	Study
SA	71	Land Application Plan of Biosolids-2006 Norm Wood Environmental Centre	SA71
From the Ground Up		2006	Plan
ST	12	Integrated Stormwater Management Plan for Holly Hills and Perkins Road Drainages	HOLLYHILL
Urban Systems	Mar.	2004	Plan
ST	14	Integrated Stormwater Management Plan – Simms Creek Watershed May-05	SIMMSFINAL
Urban Systems		2005	Plan
ST	15	Integrated Stormwater Management Plan – Willow Creek Watershed	WILLOWFINAL
Urban Systems		2005	Plan
ST	17	Integrated Stormwater Management Plan Foreshore Area	FORESHORE
Urban Systems		2005	Plan
ST	18	Integrated Stormwater Management Plan Nunns Creek Watershed	NUNNSFINAL
Urban Systems		2005	Plan
W	33	Watershed Management Plan (DCR)	WATSHED
Dayton & Knight	July	2001	Plan
W	56	C.R. Reservoir System – Integrated Recreation Plan	CAMPBELLLAKES
DLC & Assoc.	Apr.	1999	Plan
W	62	Wireless Communications Implementation Strategy	SCADA
Dayton & Knight	Apr.	2003	Strategy
W	71	Water Conservation Implementation Strategy Plan – Addendum No. 1	WATERCONSER
Stantec Consulting	May	2004	Strategy
W	78	Environmental Hydraulic Characteristics of John Hart Lake: A Pilot Study	JOHNHART
UBC Program	Feb.	2004	Study
W	82	Water Conservation Implementation Strategy Plan	W82
Stantec Consulting	June	2003	Plan
W	91	DCR Proposal for Water Supply Study	WATERSUPV2
Dayton & Knight	Aug.	1996	Study
W	92	Nunn’s Creek Master Drainage Plan	WILLOWCREMAS
Kerr Wood Leidal	Mar.	1996	Plan

 

[Remou]

I tested a similar set up with the sample records above and your code and it all seems to work for me. I suspect that there is some code elsewhere that is interferring. Have you tried copying the forms or database and simplifiying to just the code shown here?