Report Date Range

  • Thread starter Thread starter dinadvani via AccessMonster.com
  • Start date Start date
D

dinadvani via AccessMonster.com

Hi,

I have a report in which I have put in a date range, the code is below

Option Compare Database

Private Sub Command4_Click()
Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = "Accountwise Details Report"
strField = "Date Collected"

If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere = strField & " <= " & Format(Me.txtEndDate,
conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere = strField & " >= " & Format(Me.txtStartDate,
conDateFormat)
Else 'Both start and end dates.
strWhere = strField & " Between " & Format(Me.txtStartDate,
conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
End If
End If

' Debug.Print strWhere 'For debugging purposes only.
DoCmd.OpenReport strReport, acViewPreview, , strWhere
End Sub

Private Sub Command5_Click()
DoCmd.Close acForm, Me.Name

End Sub


Please advice as this pulls up all the data, irrespective of the dates
entered.

Thanks,
Da
 
Please Advice...
Hi,

I have a report in which I have put in a date range, the code is below

Option Compare Database

Private Sub Command4_Click()
Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = "Accountwise Details Report"
strField = "Date Collected"

If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere = strField & " <= " & Format(Me.txtEndDate,
conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere = strField & " >= " & Format(Me.txtStartDate,
conDateFormat)
Else 'Both start and end dates.
strWhere = strField & " Between " & Format(Me.txtStartDate,
conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
End If
End If

' Debug.Print strWhere 'For debugging purposes only.
DoCmd.OpenReport strReport, acViewPreview, , strWhere
End Sub

Private Sub Command5_Click()
DoCmd.Close acForm, Me.Name

End Sub

Please advice as this pulls up all the data, irrespective of the dates
entered.

Thanks,
Da
Click to expand...
 
dinadvani said:
I have a report in which I have put in a date range, the code is below

Option Compare Database

Private Sub Command4_Click()
Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = "Accountwise Details Report"
strField = "Date Collected"

If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere = strField & " <= " & Format(Me.txtEndDate,
conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere = strField & " >= " & Format(Me.txtStartDate,
conDateFormat)
Else 'Both start and end dates.
strWhere = strField & " Between " & Format(Me.txtStartDate,
conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
End If
End If

' Debug.Print strWhere 'For debugging purposes only.
DoCmd.OpenReport strReport, acViewPreview, , strWhere
End Sub

Private Sub Command5_Click()
DoCmd.Close acForm, Me.Name

End Sub


Please advice as this pulls up all the data, irrespective of the dates
entered.
Click to expand...


What does the Debug.Print show? The first thing that jumps
out at me is that you have a space in the field name, but
forgot to enclose it in square brackets.

strField = "[Date Collected]"
 
Back
Top