Re: Running 2 reports in a form together

Tech-Archive recommends: Fix windows errors by optimizing your registry

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 !!!






strive4peace wrote:
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 :o)

--
Message posted via http://www.accessmonster.com

.

Quantcast