Multiple Queries

[Steven.Ashby82]

Hi All

I need to run a report containing several rules.

I need to have all records with the field as "Open/Reopened" and "Pending" displayed in my report and the I want to select the date of which "Closed" records to display.

I currently have a form which allows me to search by date and Status but I need to achieve the above.

Private Sub genQuery_Click()
    Dim strSQL As String
    
    If Me.StatusCmbBox.ListIndex = -1 Then
        Call MsgBox("You have to select a Status to run the Report !", vbInformation, "Missing Information")
        Exit Sub
    End If
    
    If Len(Me.txtDateFrom & vbNullString) = 0 Then
        Call MsgBox("You have to set a Start date to run the Report !", vbInformation, "Missing Information")
        Exit Sub
    End If
    
    If Len(Me.txtDateUntil & vbNullString) = 0 Then
        Call MsgBox("You have to set a End date to run the Report !", vbInformation, "Missing Information")
        Exit Sub
    End If
    
    strSQL = "SELECT FaultTable.* FROM FaultTable WHERE ((Status = '" & StatusCmbBox & "') AND (DateRaised BETWEEN " & Format(txtDateFrom, "\#mm\/dd\/yyyy\#") & " AND " & Format(txtDateUntil, "\#mm\/dd\/yyyy\#") & "))"
    
    DoCmd.OpenReport "Monthly Report", acViewReport, OpenArgs:=strSQL
    
    Debug.Print strSQL
End Sub

 

[llkhoutx]

Build the sql string of record source query for each "if," then open the report.

 

[Steven.Ashby82]

Sorry I'm confused as to how to do this.

In theory I no longer require the combo box option I just need to be able to display.
me.status = "Open/Reopened" All calls
me.status = "Pending" All calls
me.status = "Closed" Either between specific dates using above method or with me.ClosedDate from last month

I hope someone can help

Thanks

 

[pr2-eugin]

As mentioned by llkhoutx,

Build the sql string of record source query for each "if," then open the report.

That is how you would go around doing this.. BUT.. your statement

In theory I no longer require the combo box option I just need to be able to display.

puts confusion to the problem..

So what you are saying is, if "Open/Reopened" or "Pending" is selected you do not have to worry about entering the Start date and End date? It should bother you only if "Closed" is selected? If that is the case, the following code should serve the purpose..

Private Sub genQuery_Click()
    Dim strSQL As String, strWHERE As String
    
    If Me.StatusCmbBox.ListIndex = -1 Then
        Call MsgBox("You have to select a Status to run the Report !", vbInformation, "Missing Information")
        Exit Sub
    End If
    
    strSQL = "SELECT FaultTable.* FROM FaultTable "
    
    If Me.StatusCmbBox = "Closed" Then
        If Len(Me.txtDateFrom & vbNullString) = 0 Then
            Call MsgBox("You have to set a Start date to run the Report !", vbInformation, "Missing Information")
            Exit Sub
        End If
        
        If Len(Me.txtDateUntil & vbNullString) = 0 Then
            Call MsgBox("You have to set a End date to run the Report !", vbInformation, "Missing Information")
            Exit Sub
        End If
        strWHERE = "WHERE ((Status = '" & StatusCmbBox & "') AND (DateRaised BETWEEN " & Format(txtDateFrom, "\#mm\/dd\/yyyy\#") & " AND " & Format(txtDateUntil, "\#mm\/dd\/yyyy\#") & "))"
    Else
        strWHERE = WHERE (Status = '" & StatusCmbBox & "')"
    End If
        
    DoCmd.OpenReport "Monthly Report", acViewReport, OpenArgs:=strSQL & strWHERE
End Sub

If that is not something you want, could you please explain what you mean by "I no longer require the combo box option"

 

[Steven.Ashby82]

Sorry, to clarify, I now need to run a report that contains records for all three status's .

So I need to have the one report show all open calls regardless of date and the same with pending but then I only want the show closed calls from a specific range of dates

 

[pr2-eugin]

Okay.. Then try this..

Private Sub genQuery_Click()
    Dim strSQL As String
    
    If Len(Me.txtDateFrom & vbNullString) = 0 Then
        Call MsgBox("You have to set a Start date to run the Report !", vbInformation, "Missing Information")
        Exit Sub
    End If
    
    If Len(Me.txtDateUntil & vbNullString) = 0 Then
        Call MsgBox("You have to set a End date to run the Report !", vbInformation, "Missing Information")
        Exit Sub
    End If
    
    strSQL = "SELECT FaultTable.* FROM FaultTable WHERE ([COLOR=Red](Status = 'Open/Reopened') OR (Status = 'Pending') OR[/COLOR] [COLOR=Red]([/COLOR](Status = 'Closed') AND (DateRaised BETWEEN " & Format(txtDateFrom, "\#mm\/dd\/yyyy\#") & " AND " & Format(txtDateUntil, "\#mm\/dd\/yyyy\#") & "))[COLOR=Red])[/COLOR]"
    
    DoCmd.OpenReport "Monthly Report", acViewReport, OpenArgs:=strSQL
End Sub

See the highlighted change..

 

[Steven.Ashby82]

Thanks again Paul, sorted.

 

[pr2-eugin]

Good to hear Steve..