Help combining 2 existing reports into one

[evilcowstare via AccessMonster.com]

Hi I currently have 2 seperate reports that I want to use in one.
I have...

1. A Date report that works from a form with a to and from field, it then
finds all reports between those two fields.

2. A Client report from a form, a simple drop down box that gets it info from
a query, it then works of a macro to find all records to that client

What I want is to have the one form where you can 1st select the client at
the top from the drop down and then you enter the to and from date, once you
click ok it will bring up all records for that client within the dates.

This is the code I use for the dates, is there a way to add an extra bit that
makes it look at the client combo as well to just show the records for that
client between the specified dates...

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\/yy\#"

strReport = "clientnameanddate"
strField = "DateJobReceived"

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

Debug.Print strWhere 'For debugging purposes only.

DoCmd.OpenReport strReport, acViewPreview, , strWhere
End Sub

PS I didnt write this code i just edited it for my own use so please reply in
simple terms

Any suggestions are appreciated !!!
Thank You

 

[Evi]

Lets say your combo (cboClient) with the list of clients has ClientID (a
number) as its first (hidden) column then just ammend as below changing
ClientID to your own field name

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.

Dim lngClient As Long
Dim strWhere2 As String

Const conDateFormat = "\#mm\/dd\/yy\#"

strReport = "clientnameanddate"
strField = "DateJobReceived"

If IsNull(Me.cboClient) Then
strWhere2 = ""
Else
lngClient = Me.cboClient
strWhere2 = " AND [ClientID]=" & lngClient
End if

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

If ClientID is a letter than the second 'where' will be slightly different
slightly different.

I didn't realise you could use Between in a Where string. That's going to
save me some typing!

Evi