Me.Back Again Add 3rd criteria to Date Range Selection

[ggs]

I have the code ex Allen Browne to open the report all working fine but have been struggling with using a 3rd Unbound combo box to work.
blue text is my addition, and I've been playing around with various additions to add this to the Date string lines but I have seen alot of red code and stuff that just does not work. Currently this code will filter date only or cboBranch only but when I select all 3 no luck
Help please

Private Sub cmdListLocDate1_Click()
'On Error GoTo Err_Handler 'Remove the single quote from start of this line once you have it working.
'Purpose: Filter a report to a date range.
'Documentation: http://allenbrowne.com/casu-08.html
'Note: Filter uses "less than the next day" in case the field has a time component.
Dim strReport As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#" 'Do NOT change it to match your local settings.

'DO set the values in the next 3 lines.
strReport = "rptIncListing" 'Put your report name in these quotes.
strDateField = "[date_inc]" 'Put your field name in the square brackets in these quotes.
lngView = acViewPreview 'Use acViewNormal to print instead of preview.
'Build the filter string.
If Not IsNull(Me.cboBranch) Then
strWhere = strWhere & "[BranchName]='" & Me.cboBranch & "'"
End If
If IsDate(Me.txtStartDate) Then
strWhere = "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
End If
If IsDate(Me.txtEndDate) Then


If strWhere <> vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
End If

'Close the report if already open: otherwise it won't filter properly.
If CurrentProject.AllReports(strReport).IsLoaded Then
DoCmd.Close acReport, strReport
End If

'Open the report.
'Debug.Print strWhere 'Remove the single quote from the start of this line for debugging purposes.
DoCmd.OpenReport strReport, lngView, , strWhere
Exit_Handler:
Exit Sub
Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
End If
Resume Exit_Handler
End Sub

[maximus]

can you tell me exactly what you need to do....

Then I help you with the code.

[ggs]

Hi Maximus
The Allen Browne code I have used is to select a date range for records, using two unbound boxes (short date format) txtStartDate, txtEndDate. I want to add a third criteria for selection [BranchName] and open the report to those selected records. As I say they are working seperately but once I enter the two date boxes and the cboBranchName only the dates are selected and the cboBranch is not in the Filter
thanks

[maximus]

Here is a simple code I have a from with two unbound text boxes which I type in a date Range:

Text0=StartDate
Text2=EndDate

Combo6 Contains Branch Information.

What Does the Code Do:
1) Opens Report For A Date Range.
2) Opens Report For a Branch.
3) Opens Report For a Date Range and a Branch.


Note:

You will have to type both Start and End Date for a valid Date Range. For a Particular Date Start and End Date will be the same.

Private Sub Command8_Click()
Dim strDocName As String
Dim strCriteria As String

strDocName = "Table5"

If Not IsNull(Me.Text0) Then
    If Not IsNull(Me.Text2) Then
        strCriteria = "MyDate Between #" & Me.Text0 & "# And #" & Me.Text2 & "#"
    End If
End If
If Not IsNull(Me.Combo6) Then
    If Len(strCriteria) > 0 Then
        strCriteria = strCriteria & " And BranchId=" & Me.Combo6
    Else
        strCriteria = "BranchID=" & Me.Combo6
    End If
End If

'MsgBox strCriteria

If Len(strCriteria) > 0 Then
    If CurrentProject.AllReports(strDocname).IsLoaded Then
        DoCmd.Close acReport, strDocname
    End If
    DoCmd.OpenReport strDocName, acViewPreview, , strCriteria
Else
    MsgBox "Please select Filter Criteria"
End If

[ggs]

Unfortunately this takes me back to the first problem I had with the Date not behaving according to local settings. The code from Allen browne fixed the mm/dd/yyyy dd/mm/yyyy problem I was having I just need to get the filter string to combine both the BranchName and Date

[maximus]

Please try this code the modification is in red font

Private Sub cmdListLocDate1_Click()
'On Error GoTo Err_Handler 'Remove the single quote from start of this line once you have it working.
'Purpose: Filter a report to a date range.
'Documentation: http://allenbrowne.com/casu-08.html
'Note: Filter uses "less than the next day" in case the field has a time component.
Dim strReport As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#" 'Do NOT change it to match your local settings.

'DO set the values in the next 3 lines.
strReport = "rptIncListing" 'Put your report name in these quotes.
strDateField = "[date_inc]" 'Put your field name in the square brackets in these quotes.
lngView = acViewPreview 'Use acViewNormal to print instead of preview.
'Build the filter string.

If IsDate(Me.txtStartDate) Then
strWhere = "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
End If
If IsDate(Me.txtEndDate) Then
If strWhere <> vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
End If
'-----------------------------------------------------------------------------------------------

If Not IsNull(Me.cboBranch) Then
    if len(strWhere) > 0 then
        strWhere = strWhere & " And [BranchName]='" & Me.cboBranch & "'"

    else
        strWhere="[BranchName]='" & Me.cboBranch & "'"
End If

'-----------------------------------------------------------------------------------------------



'Close the report if already open: otherwise it won't filter properly.
If CurrentProject.AllReports(strReport).IsLoaded Then
DoCmd.Close acReport, strReport
End If

'Open the report.
'Debug.Print strWhere 'Remove the single quote from the start of this line for debugging purposes.
DoCmd.OpenReport strReport, lngView, , strWhere
Exit_Handler:
Exit Sub
Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
End If
Resume Exit_Handler
End Sub

[ggs]

OK thanks BigMax (I had to add and End If that was missing) hey that was easy
Although this requires the cboBranch to be entered or nothing shows I have sorted a work around that by having two cmdButtons
Thanks for your help it is much appreciated as it also gives me a good learning experience

[bbarclay]

Where did you enter the end if to make this work?