E
evilcowstare via AccessMonster.com
Hi I have a form that runs a report based on the dates entered into two text
boxes, i need to add a Where statement like I have in my macro but need it
written into the code for the dates function.
Basically I want to add a drop-down box field as an additional filter, so it
finds in this case all the records within the two dates for a particular
client, selected in the drop down.
The drop down is called "clientdatecombo" and the form it is in is called
"clientanddate"
The combo is on the same form as the txt boxes for the date.
Ok here is the date code
Private Sub OK_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 = "DateReport"
strField = "DateJobReceived"
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
If anyone knows of a way to add the filter/Where into this so at the same
time it just shows the records for that one client selected it would be
really appreciated.
I really an a novice when it comes to code so please be gentle )
Thank You For Your Help !!!!
boxes, i need to add a Where statement like I have in my macro but need it
written into the code for the dates function.
Basically I want to add a drop-down box field as an additional filter, so it
finds in this case all the records within the two dates for a particular
client, selected in the drop down.
The drop down is called "clientdatecombo" and the form it is in is called
"clientanddate"
The combo is on the same form as the txt boxes for the date.
Ok here is the date code
Private Sub OK_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 = "DateReport"
strField = "DateJobReceived"
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
If anyone knows of a way to add the filter/Where into this so at the same
time it just shows the records for that one client selected it would be
really appreciated.
I really an a novice when it comes to code so please be gentle )
Thank You For Your Help !!!!