Re: Search Form and Checkboxes...

#1 (**permalink**) 28-Jul-2006, 08:00 AM

Try:

If ckbInvestigated Then
strWhere = strWhere & " AND " & "tblDiscrepancies.[INVESTIGATED?]"
End If

--
Steve Clark, Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

"chelsea" wrote in message
news:06E29700-58D7-4661-B74D-333BBF9BAF7C@microsoft.com...
> I'm trying to adapt a form from one of the Microsoft Templates (Issues
> Database). There is an unbound form with various text boxes, which
> filters a
> subform when the "search" command button is pressed. I've added several
> text boxes to the form to correspond with fields in my table, and they
> work
> fine. For example, the code for the Location text box in the On_Click
> command of the Search
> button is
>
> ' If Location
> If Nz(Me.txtbLocation) <> "" Then
> ' Add it to the predicate - match on leading characters
> strWhere = strWhere & " AND " & "tblDiscrepancies.LOCATION Like '*"
> & Me.txtbLocation & "*'"
> End If
>
> This works fine, and all the other text boxes seem to work fine as well.
> The problem i've run into, however, is that 3 fields in my table are
> Yes/No. I've added check boxes to the form for these fields, but i'm not
> entirely sure about the code i should use. If the user selects the check
> box, I want the search to return only those records for which the field =
> Yes. If the user does not select the check box, I want the seach to
> return
> ALL records, both Yes and No.
>
> Right now, I have
>
> ' If Investigated
> If Nz(ckbInvestigated, 0) = Yes Then
> strWhere = strWhere & " AND " & "tblDiscrepancies.INVESTIGATED? =
> Yes"
> End If
>
> for each checkbox. But when I try to run it, i get the error
>
> Runtime Error: 2448
> You can't assign a value to this object.
>
> When I debug, " Me.sbfrmDiscrepancies.Form.Filter = strWhere" is
> highlighted
> in the code.
>
> I've included the full code below:
>
> Private Sub Search_Click()
> strWhere = "1=1"
>
> ' If Survey Date From
> If IsDate(Me.txtbSurveyDateFrom) Then
> ' Add it to the predicate - exact
> strWhere = strWhere & " AND " & "tblDiscrepancies.[DATE] >= " &
> GetDateFilter(Me.txtbSurveyDateFrom)
> ElseIf Nz(Me.txtbSurveyDateFrom) <> "" Then
> strError = cInvalidDateError
> End If
>
> ' If Survey Date To
> If IsDate(Me.txtbSurveyDateTo) Then
> ' Add it to the predicate - exact
> strWhere = strWhere & " AND " & "tblDiscrepancies.[DATE] <= " &
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/9191-re-search-form-and-checkboxes.html
> GetDateFilter(Me.txtbSurveyDateTo)
> ElseIf Nz(Me.txtbSurveyDateTo) <> "" Then
> strError = cInvalidDateError
> End If
>
> ' If Resolved Date From
> If IsDate(Me.txtbResolvedDateFrom) Then
> ' Add it to the predicate - exact
> strWhere = strWhere & " AND " & "tblDiscrepancies.[DATE RESOLVED]
> >=
> " & GetDateFilter(Me.txtbResolvedDateFrom)
> ElseIf Nz(Me.txtbResolvedDateFrom) <> "" Then
> strError = cInvalidDateError
> End If
>
> ' If Resolved Date To
> If IsDate(Me.txtbResolvedDateTo) Then
> ' Add it to the predicate - exact
> strWhere = strWhere & " AND " & "tblDiscrepancies.[DATE RESOLVED]
> <=
> " & GetDateFilter(Me.txtbResolvedDateTo)
> ElseIf Nz(Me.txtbResolvedDateTo) <> "" Then
> strError = cInvalidDateError
> End If
>
> ' If Equipment
> If Nz(Me.txtbEquipment) <> "" Then
> ' Add it to the predicate - match on leading characters
> strWhere = strWhere & " AND " & "tblDiscrepancies.EQUIPMENT Like
> '*"
> & Me.txtbEquipment & "*'"
> End If
>
> ' If Building
> If Nz(Me.txtbBuilding) <> "" Then
> ' Add it to the predicate - match on leading characters
> strWhere = strWhere & " AND " & "tblDiscrepancies.BUILDING Like '*"
> & Me.txtbBuilding & "*'"
> End If
>
> ' If Floor
> If Nz(Me.txtbFloor) <> "" Then
> ' Add it to the predicate - match on leading characters
> strWhere = strWhere & " AND " & "tblDiscrepancies.FL Like '*" &
> Me.txtbFloor & "*'"
> End If
>
> ' If Location
> If Nz(Me.txtbLocation) <> "" Then
> ' Add it to the predicate - match on leading characters
> strWhere = strWhere & " AND " & "tblDiscrepancies.LOCATION Like '*"
> & Me.txtbLocation & "*'"
> End If
>
> ' If IR Survey No
> If Nz(Me.txtbIRSurveyNo) <> "" Then
> ' Add it to the predicate - match on leading characters
> strWhere = strWhere & " AND " & "tblDiscrepancies.IR_SURVEY_NO Like
> '*" & Me.txtbIRSurveyNo & "*'"
> End If
>
> ' If Item No
> If Nz(Me.txtbItemNo) <> "" Then
> ' Add it to the predicate - match on leading characters
> strWhere = strWhere & " AND " & "tblDiscrepancies.ITEM_No Like '*"
> &
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9191
> Me.txtbItemNo & "*'"
> End If
>
> ' If Investigated
> If Nz(ckbInvestigated, 0) = True Then
> strWhere = strWhere & " AND " & "tblDiscrepancies.INVESTIGATED? =
> True"
> End If
>
>
>
> If strError <> "" Then
> MsgBox strError
> Else
> 'DoCmd.OpenForm "sbfrmDiscrepancies", acFormDS, , strWhere,
> acFormEdit, acWindowNormal
> If Not Me.FormFooter.Visible Then
> Me.FormFooter.Visible = True
> DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
> End If
> Me.sbfrmDiscrepancies.Form.Filter = strWhere
> Me.sbfrmDiscrepancies.Form.FilterOn = True
> End If
> End Sub
>
>
> Any help would be appreciated!
>