Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

filtering report based on boolean value

Posted on 2010-11-27
2
Medium Priority
?
357 Views
Last Modified: 2012-05-10
Dear Experts,

I am trying to filter a report based on a field boolean value. I have a combo box on my form called cboTardy. The drop down's row source includes ("Excused";"Unexcused";"Excused & Unexecused") and is unbound. I would like to filter my report based on the combo box selection. If "Excused" is selected the report will only display records with the 'true' value (-1) in the tblStdTardyExcused field. If the user selects "Unexcused" then filter those records with a 'false' value (0) and if both "Excused & Unexcused" is select the report should ignore any tblStdTardyExcused filter (or allow either value) and tack on only the date filter.

I tried doing this (see code) but am getting an error message.

Ideas?

Thanks!
Private Sub cmdRunTardyReport_Click()
 On Error GoTo Err_cmdPrintReport_Click
    Dim iViewMode As Integer
    Dim stDocName As String
    Dim strFilter As String
 
    stDocName = "rptStudentTardy"

   
    If Not IsNull(Me![cboTardy]) Then
        If Me![cboTardy] = "Excused" Then
            strWhere = "[tblStdTardyExcused] = '" & True & "'"
        ElseIf Me![cboTardy] = "Unexcused" Then
            strWhere = "[tblStdTardyExcused] = '" & False & "'"
        Else
            strWhere = ""
    End If
        
        
        
        
    Else
        MsgBox "Please select a value from the dropdown " & vbNewLine & "box before printing this report.", vbCritical, "Value Not Selected"
      Exit Sub
    End If
    
     If IsDate(Me.txtStartDate) And IsDate(Me.txtEndDate) Then
           strWhere = strWhere & " And [tblStdTardyDate] Between #" & Me.txtStartDate & "# and #" & Me.txtEndDate & "#"
    Else
           strWhere = strWhere & "[tblStdTardyDate] = #" & Date & "#"
    End If

    
    Select Case MsgBox("Preview report before printing?", vbQuestion Or vbYesNoCancel, "Select Print Method")
        Case vbYes
          iViewMode = acViewPreview
        Case vbNo
          iViewMode = acViewNormal
        Case vbCancel
          GoTo Exit_cmdPrintReport_Click
    End Select

 
 'Close the report if already open: otherwise it won't filter properly.
    If CurrentProject.AllReports(stDocName).IsLoaded Then
        DoCmd.Close acReport, stDocName
    End If
   
   DoCmd.OpenReport stDocName, iViewMode, , strWhere
  
Exit_cmdPrintReport_Click:
    Exit Sub

Err_cmdPrintReport_Click:
    MsgBox Err.Description
    Resume Exit_cmdPrintReport_Click
 
End Sub

Open in new window

screenshot1.png
0
Comment
Question by:shogun5
2 Comments
 
LVL 24

Accepted Solution

by:
Bitsqueezer earned 2000 total points
ID: 34222117
Hi,

the question is: Which error in which line?

If you want to use boolean values you can do it like this:

strWhere = "[tblStdTardyExcused] = True"

Open in new window


(directly without single quotes and converting the "True" into numeric values)

Cheers,

Christian
0
 

Author Comment

by:shogun5
ID: 34223923
Bitsqueezer,

Thanks!

I made the following modification and all went well!

Cheers!
Private Sub cmdRunTardyReport_Click()
 On Error GoTo Err_cmdPrintReport_Click
    Dim iViewMode As Integer
    Dim stDocName As String
    Dim strFilter As String
 
    stDocName = "rptStudentTardy"

   
    If Not IsNull(Me![cboTardy]) Then
        If Me![cboTardy] = "Excused" Then
            strWhere = "[tblStdTardyExcused] = True"
        ElseIf Me![cboTardy] = "Unexcused" Then
            strWhere = "[tblStdTardyExcused] = False"
        Else
            strWhere = "[tblStdTardyExcused] = False" & " or " & "[tblStdTardyExcused] = True"
    End If
        

    Else
        MsgBox "Please select a value from the dropdown " & vbNewLine & "box before printing this report.", vbCritical, "Value Not Selected"
      Exit Sub
    End If
    
     If IsDate(Me.txtStartDate) And IsDate(Me.txtEndDate) Then
           strWhere = strWhere & " And [tblStdTardyDate] Between #" & Me.txtStartDate & "# and #" & Me.txtEndDate & "#"
    Else
           strWhere = strWhere & "[tblStdTardyDate] = #" & Date & "#"
    End If

    
    Select Case MsgBox("Preview report before printing?", vbQuestion Or vbYesNoCancel, "Select Print Method")
        Case vbYes
          iViewMode = acViewPreview
        Case vbNo
          iViewMode = acViewNormal
        Case vbCancel
          GoTo Exit_cmdPrintReport_Click
    End Select

 
 'Close the report if already open: otherwise it won't filter properly.
    If CurrentProject.AllReports(stDocName).IsLoaded Then
        DoCmd.Close acReport, stDocName
    End If
   
   DoCmd.OpenReport stDocName, iViewMode, , strWhere
  
Exit_cmdPrintReport_Click:
    Exit Sub

Err_cmdPrintReport_Click:
    MsgBox Err.Description
    Resume Exit_cmdPrintReport_Click
 
End Sub

Open in new window

0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question