query error

[Steve_in_Kent]

Have this code in a criteria of a query, and its throwing up an error.

IIf([Forms]![Frm_New_View]![Check37]=True,"*",[Forms]![Frm_New_View]![Sample])


Check 37 is a check box. just to check if its ticked, and if so, include ALL records.. otherwise whats in the field. (Sample)

[HansV]

* is used in combination with the Like operator. What do you want to accomplish? If you want the field to be equal to [Forms]![Frm_New_View]![Sample] if the check box is clear, while it can be anything if the check box is ticked, you can use

[Forms]![Frm_New_View]![Sample] OR [Forms]![Frm_New_View]![Check37]=True

in the Criteria row.

[Steve_in_Kent]

I added that in.. and it created another line in the query.. and to make it work i had to change the check on the checkbox from ''True'' to just True.

however, when i try to add more checkboxes for other fields on the form, they stop working, and won't work together.

[HansV]

How many check boxes do you have? If you have two or three of them, it's fairly easy to modify the criteria of the query to make them work together.

But if you have more, it quickly becomes a mess, and eventually the query becomes too complicated for Access to handle.

The way around this is to build the criteria in VBA. Can you tell us how you use the query? Is it the record source for a form or report, or ...?

[Steve_in_Kent]

There are 3 checkboxes total, and 3 fields. that are linked to the query. which feeds a report.

Specifically:-

testfor is linked to Check39

product is linked to Check42

[HansV]

I'd remove the criteria from the three fields, and use VBA.
I'll use the following names - replace them with the actual names:

Report: rptMyReport
Field to be filtered if Check37 is clear: Field1
Field to be filtered if Check39 is clear: Field2
Field to be filtered if Check42 is clear: Field3
Command button to open the report: cmdReport

Code: Select all

Private Sub cmdReport_Click()
    Dim strWhere As String
    If Me.Check37 = False Then
        strWhere = " AND [Field1]=" & Chr(34) & Me.Sample & Chr(34)
    End If
    If Me.Check39 = False Then
        strWhere = strWhere & " AND [Field2]=" & Chr(34) & Me.TestFor & Chr(34)
    End If
    If Me.Check42 = False Then
        strWhere = strWhere & " AND [Field3]=" & Chr(34) & Me.Product & Chr(34)
    End If
    If strWhere <> "" Then
        strWhere = Mid(strWhere, 6)
    End If
    DoCmd.OpenReport ReportName:="rptMyReport", View:=acViewPreview, WhereCondition:=strWhere
End Sub

The & Chr(34) are used to enclose text values in double quotes. If one or more of the fields is a number field, remove the & Chr(34) for those.