How to add selection to report form

[paul1707]

Hi All,

I have a form frmInstSelJob which i would like to firstly select an option from a cbo and then use the code below to do the date range for the report.

Could someone please point me in the right direction, i have been looking at this for the last 6 hours and can't make sense of it.


Private Sub Command6_Click()
Dim strReport As String
Dim strField As String
Dim strWhere As String
Const conDateFormat = "\#mm\/dd\/yyyy\#"
strReport = "rptAllJobsDate"
strField = "SchedInstallDate"
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

DoCmd.Close acForm, Me.Name

End Sub

Thank you.

 

[DCrake]

Simple Software Solutions

When you debug.print the strwhere have you got #'s around the dates,vis

Between #01/01/2008# And #01/02/2008#

If not you need to add them to your strWhere build string.

Seems to me it cannot evaluate the where condition correctly.

CodeMaster:

 

[paul1707]

When you debug.print the strwhere have you got #'s around the dates,vis

Between #01/01/2008# And #01/02/2008#

If not you need to add them to your strWhere build string.

Seems to me it cannot evaluate the where condition correctly.

CodeMaster:

Hi CodeMaster,

This code works fine, i was hoping to add the cbo box to the code to enable a form filter for a report. I worked out another way of doing it.

Yes there were #'s around the date.

Thanks again