Re: [MS_AccessPros] Filter Dialog for report form

 

Actually when I worked through the original code, I changed all the strState to strStatus and it seems to work but the only issue was if I left both chkActive and chkInactive blank it is suppose to return all employees regardless of their status but it only returns the Inactive employee (chkInactive = 0 ). Here is the code now:
 
  Dim strReport As String
  Dim strWhere As String
  Dim strStatus As String
  Dim strWhereDate As String
 
 
  'This is one way you can build a SQL "WHERE" statement.
  'Active employee
  If Me.chkActive = True Then
    strStatus = strStatus & ",-1"
  End If
 
  'Inactive employee
  If Me.chkActive = False Then
    strStatus = strStatus & ",0"
  End If
 
  ' If the first part of strState is ,
  ' We know that strState has a value to use
  If Left(strStatus, 1) = "," Then
        ' Remove the ,
       
    strStatus = Right(strStatus, Len(strStatus) - 1)
        ' Add the beginning part of the Where Statement
    strStatus = " AND ActiveFlag IN (" & strStatus
        ' Add the end part of the Where Statement
        strStatus = strStatus & ")"
  Else
        ' If the , was not added to strState then
        ' No value was there--clear strState
        strStatus = vbNullString
  End If
   ' Put together the SQL string
    strWhere = strWhereDate & strStatus
'    Debug.Print "strWhere = " & strWhere
    ' Trim "AND " from the beginning if needed
    If Left(strWhere, 4) = " AND" Then
'        intWhereLen = Len(strWhere) - 4
        strWhere = Right(strWhere, Len(strWhere) - 4)
    End If
'    Debug.Print "strWhere = " & strWhere
    strReport = Me.lstReports
'    Debug.Print "strReport = " & strReport
    DoCmd.OpenReport strReport, View:=acViewPreview, WhereCondition:=strWhere
   
    ' To use these 2 lines of code below you must add
    ' DoCmd.Restore to your Report's On Close Event,
    DoCmd.RunCommand acCmdZoom75
    DoCmd.Maximize
Exit_cmdPreviewReport_Click:
    Exit Sub
Err_cmdPreviewReport_Click:
    Select Case Err.Number
        Case Is = 2501
            'Ignore Useless Error Messsage
            Resume Next
        Case Is = 2046
            'Restore to Normal Window size
            DoCmd.Restore
        Case Else
            Call MsgBox(Err.Description & vbCrLf & "Error Number: " & Err.Number & vbCrLf & _
            " In procedure cmdPreviewReport_Click of VBA Document Form_Report Center")
            Resume Exit_cmdPreviewReport_Click
    End Select

I am also going to need to add a Location to the where clause. The field in LocationName on the report. Entries will be liek TAP, CANYONS, GAMING, Etc. So when they are using the filter screen they might check chkActive and check chkTAPS which would meean they want to see all active employee who work at TAPS.

With warm regards,

Arthur Lorenzini| SQL Server/Access Developer l alorenzin@live.com
Office: 605-338-0947| Mobile: 605-857-9137 | Fax: 605-338-0947
 
1316 E. 7th Street
Sioux Falls, SD  57103  
SQL Server Development
Database Adminstration Services
Microsoft Access Development  
Grant Writing TA Services
IT Assessment Services
Software Application Training

From: John Viescas <john@viescas.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Friday, August 26, 2011 10:27 AM
Subject: RE: [MS_AccessPros] Filter Dialog for report form

 
Art-

Well, that example is doing a "custom filter by form" using several check boxes
to select states and a date filter. If all you have is one check box, then you
probably want a filter like:

strWhere = "[ActiveFlag] = " & Me.chkActive
DoCmd.OpenReport "All Employee Report", acViewPreview, _
WhereCondition:=strWhere

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Art
Sent: Friday, August 26, 2011 5:02 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Filter Dialog for report form

I am trying to develop a filter dialog form for a filtering report. I have found
a few examples but I don't understand them. THis is the code I found for one of
them but I am not sure how to adapt it.

Report is called All Employee Report.

I have a yes/no field called ActiveFlag. I would like to filter the report based
on this. The code I found is as follows but its working with strings which is
confusing me.

Private Sub cmdOpenReport_Click()
On Error GoTo Err_cmdOpenReport_Click

Dim strReport As String
Dim strWhere As String
Dim strState As String
Dim strWhereDate As String

' This is one way you can build a SQL "Where" Statement
If Me.chkCalifornia = True Then
strState = strState & ",'CA'"
End If

If Me.chkFlorida = True Then
strState = strState & ",'FL'"
End If

If Me.chkNorthCarolina = True Then
strState = strState & ",'NC'"
End If

If Me.chkTennessee = True Then
strState = strState & ",'TN'"
End If

' If the first part of strState is ,
' We know that strState has a value to use
If Left(strState, 1) = "," Then
' Remove the ,

strState = Right(strState, Len(strState) - 1)

' Add the beginning part of the Where Statement
strState = " AND State IN (" & strState

' Add the end part of the Where Statement
strState = strState & ")"
Else
' If the , was not added to strState then
' No value was there--clear strState
strState = vbNullString
End If

' Debug.Print "strState = " & strState

' Build the Date Filter
Select Case cboDateFilter
Case Is = "MarriageDate"
strWhereDate = "((tblPeople.MarriageDate) >=
[Forms]![frmReportDialog]![txtStartDate])" & _
" AND
((tblPeople.MarriageDate)<=[Forms]![frmReportDialog]![txtEndDate])"
Case Is = "DOB"
strWhereDate = "((tblPeople.DOB) >=
[Forms]![frmReportDialog]![txtStartDate])" & _
" AND ((tblPeople.DOB)<=[Forms]![frmReportDialog]![txtEndDate])"
Case Is = "None"
strWhereDate = vbNullString
Case Else
strWhereDate = vbNullString
End Select

' Debug.Print "strWhereDate = " & strWhereDate

' Put together the SQL string
strWhere = strWhereDate & strState
' Debug.Print "strWhere = " & strWhere

' Trim "AND " from the beginning if needed
If Left(strWhere, 4) = " AND" Then
' intWhereLen = Len(strWhere) - 4
strWhere = Right(strWhere, Len(strWhere) - 4)
End If
' Debug.Print "strWhere = " & strWhere

strReport = Me.lstReports
' Debug.Print "strReport = " & strReport

DoCmd.OpenReport strReport, View:=acViewPreview, WhereCondition:=strWhere

' To use these 2 lines of code below you must add
' DoCmd.Restore to your Report's On Close Event,
DoCmd.RunCommand acCmdZoom75
DoCmd.Maximize

Exit_cmdOpenReport_Click:
Exit Sub

Err_cmdOpenReport_Click:
Select Case Err.Number
Case Is = 2501
'Ignore Useless Error Messsage
Resume Next
Case Is = 2046
'Restore to Normal Window size
DoCmd.Restore
Case Else
Call MsgBox(Err.Description & vbCrLf & "Error Number: " & Err.Number
& vbCrLf & _
" In procedure cmdOpenReport_Click of VBA Document
Form_frmReportDialog")
Resume Exit_cmdOpenReport_Click
End Select

End Sub

I don't need any of the date filters but I need to adjust it for the checkbox.

Thanks,

Aert Lorenzini
Sioux Falls. SD

------------------------------------

Yahoo! Groups Links

[Non-text portions of this message have been removed]

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (3)

Recent Activity:

• New Members 3
• New Files 2

Visit Your Group

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___