Running 2 reports in a form together

[evilcowstare via AccessMonster.com]

Hi ,
I have 2 seperate reports, one works off a drop down menu where you select a
client and it brings up all the jobs to the selected clients name. The 2nd is
a date report where you enter 2 dates and it brings up all the jobs between
them.
What I want to do is make them now work together, so 1st you select the
client then the dates and it brings up all the jobs for that client between
the dates selected.

The client report uses a "where" command, of client name=clientcombo for
example.

The date is written in code, now is there anywhere I can add a where command
or similar to the date code so that it filters the date results by the client
name on the combo...

The date code is ...

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

Thanks to anyone who helps )

 

[strive4peace]

building OpenReport Where parameter
---

here is an example that tests criteria and builds a filter string to use
as a parameter in OpenReport

assuming you are in the code behind the ReportMenu form...

'~~~~~~~~~~~~~~~~~~

'tell Access you are going to create a variable to hold text
dim mFilter as string

'initialize the variable
mFilter = ""

'substitute YOUR controlname in here after "Me."
'we are testing to see if it is filled out
'if it is, we are going to make mFilter hold the criteria
If not IsNull(me.text_controlname ) Then
mfilter = "[TextFieldname]= '" & me.text_controlname & "'"
end if

'test the next control
If not IsNull(me.date_controlname ) Then
'if we alread have criteria, add AND to specify that and more
if mfilter <> "" then mFilter = mFilter & " AND "
mFilter = mFilter & "[DateFieldname]= #" _
& me.date_controlname & "#"
end if

'test the next control
If not IsNull(me.numeric_controlname ) Then
'if we alread have criteria, add AND to specify that and more
if mfilter <> "" then mFilter = mFilter & " AND "
mfilter = mfilter & "[NumericFieldname]= " _
& me.numeric_controlname
end if

'----------------------- listbox
dim varItem as Variant _
, mListWhere as string
mListWhere = ""

For Each varItem In me.listbox_controlname.ItemsSelected

'delete the line that doesn't apply

'for text
mListWhere = mListWhere & "'" _
& me.listbox_controlname.ItemData(varItem) & "', "

'for numbers
mListWhere = mListWhere _
& me.listbox_controlname.ItemData(varItem) & ", "

Next varItem

if len(mListWhere) > 0 then
mListWhere = "[Field_Name] IN (" & mListWhere

'remove comma and space from the end and add a parenthesis
mListWhere = left(mListWhere,len(mListWhere)-2)) & ")"

'if we alread have criteria, add AND to specify that and more
if mfilter <> "" then mFilter = mFilter & " AND "

mfilter = mfilter & mListWhere
end if
'-----------------------

DoCmd.OpenReport "ReportName", acViewPreview, , mfilter

'~~~~~~~~~~~~~~~~~~

I have used
TextFieldname to show how text needs to be delimited - with single quote
marks (you can also use double quote marks

DateFieldname to show that dates need to be delimited with #

NumericFieldname to show that numbers are not delimited

for listbox criteria, a loop is done through the selected items and the
criteria is listed and seperated by commas

'~~~~~~~~~~~~~~~~~~
each time, we are testing to see if a filter control is filled out.
If it is, we are going to see if we first need to add AND (if the filter
string already says something)
Then we are going to add the criteria for that filter
the first condition tested, obviously, does not have anything in the
filter string yet <smile>

make sure that the referenced fields are in the underlying recordset for
the report.
Since a filter is applied on the recordset, they do not have to be on
the report object (unlike a form)

For a Date Range, you would do:

'~~~~~~~~~~~~~~~~~~

If not IsNull(me.date1_controlname ) Then
mFilter = mFilter & "[DateFieldname]>= #" _
& me.date1_controlname & "#"
end if

If not IsNull(me.date2_controlname ) Then
'if we alread have criteria, add AND to specify that and more
if mfilter <> "" then mFilter = mFilter & " AND "
mFilter = mFilter & "[DateFieldname] <= #" _
& me.date2_controlname & "#"
end if
'~~~~~~~~~~~~~~~~~~

If you are not using the American Date Format, then you will need to do
something like this:

Format(me.date_controlname, "\#m\/d\/yyyy\#")
instead of
"#" & me.date_controlname & "#"

hopefully, you can adapt this logic to your question -- if not, we can
help you further

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[evilcowstare via AccessMonster.com]

Hi Crystal, thanks very much for replying. To be honest I feel I am totally
over my head on this one, Im a bit of a novice when it comes to code, even
the date code i showed in my thread was written for me so Im really
struggling at the moment to apply all the code you put and know what to do
with it, maybe if I give you all my details you could apply them for me?
Sorry I know its a bit much I really need this function to work I just feel
this is quite a few steps abovre where I am at the moment.

Form where the report is run from is called "clientanddate"
It contains 1 drop-down box for the clients name, this is collected from a
query called "Clients Query"
the drop-down is called "clientdatecombo"
I also have 2 text boxes for the date, one for the from date and one for the
too date.
these are named..
txtStartDate and txtEndDate
next to them is an OK button which at the moment just runs the report based
on the dates entered, it does this with this 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

What I need it to do is also filter in the date report using the clients name
only from "clientdatecombo" so it brings up all the jobs between the dates
for that client.

Im really sorry if I am being really stupid on this and I know I should just
learn it and I am trying it is just really difficult if you miss a big chunk
out the middle.

Thank You for any help you or anyone else can give, I really REALLY!
appreciate it !!!

building OpenReport Where parameter
---

here is an example that tests criteria and builds a filter string to use
as a parameter in OpenReport

assuming you are in the code behind the ReportMenu form...

'~~~~~~~~~~~~~~~~~~

'tell Access you are going to create a variable to hold text
dim mFilter as string

'initialize the variable
mFilter = ""

'substitute YOUR controlname in here after "Me."
'we are testing to see if it is filled out
'if it is, we are going to make mFilter hold the criteria
If not IsNull(me.text_controlname ) Then
mfilter = "[TextFieldname]= '" & me.text_controlname & "'"
end if

'test the next control
If not IsNull(me.date_controlname ) Then
'if we alread have criteria, add AND to specify that and more
if mfilter <> "" then mFilter = mFilter & " AND "
mFilter = mFilter & "[DateFieldname]= #" _
& me.date_controlname & "#"
end if

'test the next control
If not IsNull(me.numeric_controlname ) Then
'if we alread have criteria, add AND to specify that and more
if mfilter <> "" then mFilter = mFilter & " AND "
mfilter = mfilter & "[NumericFieldname]= " _
& me.numeric_controlname
end if

'----------------------- listbox
dim varItem as Variant _
, mListWhere as string
mListWhere = ""

For Each varItem In me.listbox_controlname.ItemsSelected

'delete the line that doesn't apply

'for text
mListWhere = mListWhere & "'" _
& me.listbox_controlname.ItemData(varItem) & "', "

'for numbers
mListWhere = mListWhere _
& me.listbox_controlname.ItemData(varItem) & ", "

Next varItem

if len(mListWhere) > 0 then
mListWhere = "[Field_Name] IN (" & mListWhere

'remove comma and space from the end and add a parenthesis
mListWhere = left(mListWhere,len(mListWhere)-2)) & ")"

'if we alread have criteria, add AND to specify that and more
if mfilter <> "" then mFilter = mFilter & " AND "

mfilter = mfilter & mListWhere
end if
'-----------------------

DoCmd.OpenReport "ReportName", acViewPreview, , mfilter

'~~~~~~~~~~~~~~~~~~

I have used
TextFieldname to show how text needs to be delimited - with single quote
marks (you can also use double quote marks

DateFieldname to show that dates need to be delimited with #

NumericFieldname to show that numbers are not delimited

for listbox criteria, a loop is done through the selected items and the
criteria is listed and seperated by commas

'~~~~~~~~~~~~~~~~~~
each time, we are testing to see if a filter control is filled out.
If it is, we are going to see if we first need to add AND (if the filter
string already says something)
Then we are going to add the criteria for that filter
the first condition tested, obviously, does not have anything in the
filter string yet <smile>

make sure that the referenced fields are in the underlying recordset for
the report.
Since a filter is applied on the recordset, they do not have to be on
the report object (unlike a form)

For a Date Range, you would do:

'~~~~~~~~~~~~~~~~~~

If not IsNull(me.date1_controlname ) Then
mFilter = mFilter & "[DateFieldname]>= #" _
& me.date1_controlname & "#"
end if

If not IsNull(me.date2_controlname ) Then
'if we alread have criteria, add AND to specify that and more
if mfilter <> "" then mFilter = mFilter & " AND "
mFilter = mFilter & "[DateFieldname] <= #" _
& me.date2_controlname & "#"
end if
'~~~~~~~~~~~~~~~~~~

If you are not using the American Date Format, then you will need to do
something like this:

Format(me.date_controlname, "\#m\/d\/yyyy\#")
instead of
"#" & me.date_controlname & "#"

hopefully, you can adapt this logic to your question -- if not, we can
help you further

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hi ,
I have 2 seperate reports, one works off a drop down menu where you select a

[quoted text clipped - 45 lines]

Thanks to anyone who helps )

 

[strive4peace]

Hi (what is your name),

In order to explain things to you, you need to get some basics under
your belt. To help you understand Access a bit better, send me an email
and request my 30-page Word document on Access Basics (for Programming)
-- it doesn't cover VBA, but prepares you for it because it covers
essentials in Access. I do also send out the first 3 chapters of a
book I am writing on VBA to all who request it.

Be sure to put "Access Basics" in the subject line so that I see your
message...

whenever someone gives you code, you need to take the time to understand
each line -- and ask questions if you don't

then, after you read the document, more will make sense -- ask what you
are still confused about.


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hi Crystal, thanks very much for replying. To be honest I feel I am totally
over my head on this one, Im a bit of a novice when it comes to code, even
the date code i showed in my thread was written for me so Im really
struggling at the moment to apply all the code you put and know what to do
with it, maybe if I give you all my details you could apply them for me?
Sorry I know its a bit much I really need this function to work I just feel
this is quite a few steps abovre where I am at the moment.

Form where the report is run from is called "clientanddate"
It contains 1 drop-down box for the clients name, this is collected from a
query called "Clients Query"
the drop-down is called "clientdatecombo"
I also have 2 text boxes for the date, one for the from date and one for the
too date.
these are named..
txtStartDate and txtEndDate
next to them is an OK button which at the moment just runs the report based
on the dates entered, it does this with this 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

What I need it to do is also filter in the date report using the clients name
only from "clientdatecombo" so it brings up all the jobs between the dates
for that client.

Im really sorry if I am being really stupid on this and I know I should just
learn it and I am trying it is just really difficult if you miss a big chunk
out the middle.

Thank You for any help you or anyone else can give, I really REALLY!
appreciate it !!!

building OpenReport Where parameter
---

here is an example that tests criteria and builds a filter string to use
as a parameter in OpenReport

assuming you are in the code behind the ReportMenu form...

'~~~~~~~~~~~~~~~~~~

'tell Access you are going to create a variable to hold text
dim mFilter as string

'initialize the variable
mFilter = ""

'substitute YOUR controlname in here after "Me."
'we are testing to see if it is filled out
'if it is, we are going to make mFilter hold the criteria
If not IsNull(me.text_controlname ) Then
mfilter = "[TextFieldname]= '" & me.text_controlname & "'"
end if

'test the next control
If not IsNull(me.date_controlname ) Then
'if we alread have criteria, add AND to specify that and more
if mfilter <> "" then mFilter = mFilter & " AND "
mFilter = mFilter & "[DateFieldname]= #" _
& me.date_controlname & "#"
end if

'test the next control
If not IsNull(me.numeric_controlname ) Then
'if we alread have criteria, add AND to specify that and more
if mfilter <> "" then mFilter = mFilter & " AND "
mfilter = mfilter & "[NumericFieldname]= " _
& me.numeric_controlname
end if

'----------------------- listbox
dim varItem as Variant _
, mListWhere as string
mListWhere = ""

For Each varItem In me.listbox_controlname.ItemsSelected

'delete the line that doesn't apply

'for text
mListWhere = mListWhere & "'" _
& me.listbox_controlname.ItemData(varItem) & "', "

'for numbers
mListWhere = mListWhere _
& me.listbox_controlname.ItemData(varItem) & ", "

Next varItem

if len(mListWhere) > 0 then
mListWhere = "[Field_Name] IN (" & mListWhere

'remove comma and space from the end and add a parenthesis
mListWhere = left(mListWhere,len(mListWhere)-2)) & ")"

'if we alread have criteria, add AND to specify that and more
if mfilter <> "" then mFilter = mFilter & " AND "

mfilter = mfilter & mListWhere
end if
'-----------------------

DoCmd.OpenReport "ReportName", acViewPreview, , mfilter

'~~~~~~~~~~~~~~~~~~

I have used
TextFieldname to show how text needs to be delimited - with single quote
marks (you can also use double quote marks

DateFieldname to show that dates need to be delimited with #

NumericFieldname to show that numbers are not delimited

for listbox criteria, a loop is done through the selected items and the
criteria is listed and seperated by commas

'~~~~~~~~~~~~~~~~~~
each time, we are testing to see if a filter control is filled out.
If it is, we are going to see if we first need to add AND (if the filter
string already says something)
Then we are going to add the criteria for that filter
the first condition tested, obviously, does not have anything in the
filter string yet <smile>

make sure that the referenced fields are in the underlying recordset for
the report.
Since a filter is applied on the recordset, they do not have to be on
the report object (unlike a form)

For a Date Range, you would do:

'~~~~~~~~~~~~~~~~~~

If not IsNull(me.date1_controlname ) Then
mFilter = mFilter & "[DateFieldname]>= #" _
& me.date1_controlname & "#"
end if

If not IsNull(me.date2_controlname ) Then
'if we alread have criteria, add AND to specify that and more
if mfilter <> "" then mFilter = mFilter & " AND "
mFilter = mFilter & "[DateFieldname] <= #" _
& me.date2_controlname & "#"
end if
'~~~~~~~~~~~~~~~~~~

If you are not using the American Date Format, then you will need to do
something like this:

Format(me.date_controlname, "\#m\/d\/yyyy\#")
instead of
"#" & me.date_controlname & "#"

hopefully, you can adapt this logic to your question -- if not, we can
help you further

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hi ,
I have 2 seperate reports, one works off a drop down menu where you select a

[quoted text clipped - 45 lines]

Thanks to anyone who helps )