Allen Browns Date Range + Combo Box for Client and Project Report Filter Coding Help

[pbaldy]

So the code that errors isn't here? The way to debug things like this is:

http://www.baldyweb.com/ImmediateWindow.htm

so add your code for PEBLO and un-comment that line for strWhere so you can see what the final string is. If you don't spot the problem, post it here.

[Ahsan]

So the code that errors isn't here? The way to debug things like this is:

http://www.baldyweb.com/ImmediateWindow.htm

so add your code for PEBLO and un-comment that line for strWhere so you can see what the final string is. If you don't spot the problem, post it here.

SAME PROBLEM I AM FACING HERE GETTING DEBUGGING THIS LINE "DoCmd.OpenReport strReport, lngView, , strWhere" KINDLY HELP ME OUT I AM NOT GETTING IT BECAUSE I AM NEW TO VBA AND ACCESS.

[Ahsan]

HERE IS MY WHOLE CODE. I WNAT TO FILTER COMBO CITY WITH DATE RANGE AS WELL.
'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 = "STORES SALES WISE REPORT" 'Put your report name in these quotes.
strDateField = "[DATE]" 'Put your field name in the square brackets in these quotes.
lngView = acViewReport '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) & ") AND"
End If

If Not IsNull(Me.CBO_CITY) Then
' strWhere = strWhere & "([CITY] = " & Me.[CBO_CITY] & ")"
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

[pbaldy]

What is the result of the Debug line?

[ssanfu]

@Ahsan &
@ARickert

By posting your questions under bbarclay's post, you have done what is called hi-jacking a post. You will get fewer views and thus fewer options for a solution.

You should start your own post and link to bbarclay's (if you want) if you have a similar problem/question instead of taking over his post.

[Ahsan]

What is the result of the Debug line?

THE result is that it doesnot filter my combo selection.

would you please modify my code so that i can filter date range as well as combo selections at a time on click of APPLY FILTER button. THE date range filter works fine. but i want combo selection and date selection to be filter at a time.




Private Sub cmdPreview_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 = "STORES SALES WISE REPORT" 'Put your report name in these quotes.
strDateField = "[DATE]" 'Put your field name in the square brackets in these quotes.
lngView = acViewReport '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

'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


PLZZZZ HELP ME OUT I AM IN A BIG TROUBLE.

[pbaldy]

Read the post you quoted to start with again. I want to see what strWhere contains. I think seeing it will lead you to the solution.