Final Report

[Guest]

Hi I have a list box that is based on a field list. there are 8 field I can
choose from, once one is clicked its values get transfered to a near by combo
box. Once the valuse is chosen it then filters another list box(List4) with
the records that have the specified value in that certain field. What I
would like to do is link a report to the either of the lists to have a way to
show the information. is it possible also to change the title of the report
each time a new field is chosen..

PLEASE HELP LAST SMALL BIT OF CODING LEFT ON PROJECT...

 

[strive4peace]

Hi Big Red,

here is some information you can use to pattern after.

Report Filters

It would be best to build a filter string for the report (as opposed to
imbedding a parameter in a query)--in this way, you can use comboboxes
and listboxes to make it easier for the user to choose criteria and you
can ignore criteria when it has not been specified...

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.controlname_for_text & "'"
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.controlname_for_date & "#"
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.controlname_for_number
end if


if len(mfilter) > 0 then
DoCmd.OpenReport "ReportName", acViewPreview, , mfilter
else
DoCmd.OpenReport "ReportName", acViewPreview
endif
'~~~~~~~~~~~~~~~~~~

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

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


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.controlname_for_date1 & "#"
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.controlname_for_date2 & "#"
end if
'~~~~~~~~~~~~~~~~~~
***

IF you want to also PRINT the criteria on the report

put a LABEL on your PageFooter
name --> Label_Criteria

then, in the OnFormat event of the ReportHeader

'~~~~~~~~~~~~~~~~~~
if len(trim(nz(me.filter,""))) > 0 then
me.Label_Criteria.Caption = me.filter
me.Label_Criteria.Visible = true
else
me.Label_Criteria.Visible = false
end if
'~~~~~~~~~~~~~~~~~~



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