yes/no filter

[slimjen]

All, using ms access 2010. I have a form recordset of a query. I have two fields
that are set up in the table as yes/no fields. When I filter this field on the form; the
drop down is yes no but upon selection it's -1 or 0. When I click to view the
report; it gives me all the records or the message I set up " there's
no criteria for this report". All other filter fields are fine. Is there
something else I have to do to be able to filter yes/no fields? Here's a snippet of my code:

Dim strSQL As String, intCounter As Integer
Dim db As Database, rs As Recordset
Dim ctl As Control, strname As String, strnewquery As String
Dim strRptSel As String
Dim stMessage As String
Set db = CurrentDb

      'Build SQL String
      For Each ctl In Me.Form
      If ctl.Tag = "input" Then
      strname = "me." & ctl.Name
      If ctl.Value > "" Then
      strSQL = strSQL & "[" & ctl.Name & "] " & " like " & Chr(34) & ctl.Value & Chr(34) & " And "
      End If
      End If
     Next ctl
      ' Set the value of the parameter.
    
    If Me.cboWEdateFrom & vbNullString <> "" And Me.cboWEdateTo & vbNullString <> "" Then
        strSQL = strSQL & ("[inQADateStamp] BETWEEN #" & cboWEdateFrom & "# And #" & cboWEdateTo & "# And " & "'")
   End If

I posted something simular on another forum but no responses. I need help before I put this in production today. Thanks

[June7]

Need additional code to concatenate the criteria for the boolean values, like you have for the date criteria.

Don't use Like with the booleans, use =. Also, the yes/no fields are not text so can't have apostrophe delimiter.

Why are you using the Like operator anyway? It is meaningless without wildcard, might as well just use =.

[slimjen]

ok. This is what my code looks like now:

Dim strSQL As String, intCounter As Integer, strWhere As String
Dim db As Database, rs As Recordset
Dim ctl As Control, strname As String, strnewquery As String
Dim strRptSel As String
Dim stMessage As String
Set db = CurrentDb

      'Build SQL String
      For Each ctl In Me.Form
      If ctl.Tag = "input" Then
      strname = "me." & ctl.Name
      If ctl.Value > "" Then
      strSQL = strSQL & "[" & ctl.Name & "] " & " like " & Chr(34) & ctl.Value & Chr(34) & " And "
      End If
      End If
     Next ctl
      ' Set the value of the parameter.
    
    If Me.cboWEdateFrom & vbNullString <> "" And Me.cboWEdateTo & vbNullString <> "" Then
        strSQL = strSQL & ("[inQADateStamp] BETWEEN #" & cboWEdateFrom & "# And #" & cboWEdateTo & "# And " & "'")
   End If
   
       If Me.Priority = -1 Then
        strWhere = strWhere & "([QA Priority Bin] = True) AND "
    ElseIf Me.Priority = 0 Then
        strWhere = strWhere & "([QA Priority Bin] = False) AND "
    End If
     
    strnewquery = "Select qryQAReport.* FROM qryQAReport"
    If strSQL <> "" Then
        strSQL = Left(strSQL, (Len(strSQL) - 5))
        strnewquery = strnewquery & " WHERE " & strSQL & ";"
    End If
    Debug.Print strnewquery
    ' Create the recordset
    Set rs = db.OpenRecordset(strnewquery)
    If rs.RecordCount > 0 Then
    DoCmd.OpenReport "rptQAReport", acViewPreview, , strSQL
     
'          DoCmd.OutputTo acOutputReport, "rptQAReport", acFormatXLS, True
'        DoCmd.Close acForm, "frmReportbuilder"
    Else
        MsgBox "There are no records that match your criteria! Please select new criteria.", vbInformation
        Exit Sub
    End If

Still only returns no when i select yes???

[June7]

These criteria input controls are all unbound, including the checkbox?

Should work.

If you always want the checkbox value to be applied in the filter, can do simply:
strSQL = strSQL & "([QA Priority Bin]=" & Me.checkbox & ") AND "

If you want the yes/no field to be considered in the filter only if the checkbox is checked:

If Me.Priority = -1 Then
strSQL = strSQL & "([QA Priority Bin]=-1 AND "
End If

You said there were two yes/no fields? Can records have both checked or unchecked? If only one can be checked then really should be only one field with two value options.

EDIT: see Steve's post below, I did not catch the change in variable name, oops

[slimjen]

Yes; the input controls are all unbound with the recordsource of the field from the query. Is the recordsource the problem? I will have to get back to this on Monday.

[ssanfu]

Still only returns no when i select yes???

If you are talking about "Me.Priority", you changed the variable from "strSQL" to "strWhere".


I modified your code a little.

   Dim db As DAO.Database, rs As DAO.Recordset
   Dim ctl As Control
   Dim strSQL As String, strWhere As String
   Dim strname As String, strnewquery As String
   Dim strRptSel As String, stMessage As String
   Dim intCounter As Integer

   Set db = CurrentDb

   'Build SQL String
   For Each ctl In Me.Form
      If ctl.Tag = "input" Then
         '         strname = "me." & ctl.Name   ' <<< variable not used - doesn't do anything
         If ctl.Value > "" Then
            '            strSQL = strSQL & "[" & ctl.Name & "] " & " like " & Chr(34) & ctl.Value & Chr(34) & " And "
            strSQL = strSQL & "[" & ctl.Name & "] " & " = " & Chr(34) & ctl.Value & Chr(34) & " And "   ' changed to equals
         End If
      End If
   Next ctl
   ' Set the value of the parameter.

   If Me.cboWEdateFrom & vbNullString <> "" And Me.cboWEdateTo & vbNullString <> "" Then
      '      strSQL = strSQL & ("[inQADateStamp] BETWEEN #" & cboWEdateFrom & "# And #" & cboWEdateTo & "# And " & "'") ' extra single quote
      strSQL = strSQL & ("[inQADateStamp] BETWEEN #" & cboWEdateFrom & "# And #" & cboWEdateTo & "# And ")
   End If

   'Why did you switch from "strSQL" to "strWhere"?
   '   If Me.Priority = -1 Then
   '      strWhere = strWhere & "([QA Priority Bin] = True) AND "
   '   ElseIf Me.Priority = 0 Then
   '      strWhere = strWhere & "([QA Priority Bin] = False) AND "
   '   End If

'the above IF() can be one line
   'changed to strSQL
   strSQL = strSQL & "([QA Priority Bin] = " & Me.Priority & ") AND "


   strnewquery = "Select qryQAReport.* FROM qryQAReport"
   If strSQL <> "" Then
      strSQL = Left(strSQL, (Len(strSQL) - 5))
      strnewquery = strnewquery & " WHERE " & strSQL & ";"
   End If

'   Debug.Print strnewquery

   ' Create the recordset
   Set rs = db.OpenRecordset(strnewquery)

   If rs.RecordCount > 0 Then
      DoCmd.OpenReport "rptQAReport", acViewPreview, , strSQL

      '          DoCmd.OutputTo acOutputReport, "rptQAReport", acFormatXLS, True
      '        DoCmd.Close acForm, "frmReportbuilder"
   Else
      MsgBox "There are no records that match your criteria! Please select new criteria.", vbInformation
      Exit Sub
   End If

   'clean up
   rs.Close
   Set rs = Nothing
   Set db = Nothing

Note: using DAO, a recordset recordcount is not accurate until you reach the end of the record set, either by looping through or using "rs.MoveLast".
If, immediately after opening a record set, you do not move to the last record, the record count will be 1.
Just an FYI. I had problems with this until I realized I had to move to the last record to get a true record count.

[slimjen]

Great. I'll try this today. thank you for your response.