Filtering a report

[crescent-centre]

I have a form which is the main data entry for a document control db. Some of the documents have a status of approved and some not approved (also a few other possibilities).

If I open a report in design view I can filter the report with a line
docstatus like "approved".

I have added a button on the form which I want to do the above, i.e. display just the approved docs. Using previous help from this site I have added the following code.

stDocName = "Report1"
DoCmd.OpenReport stDocName, acViewDesign
Report_report1.Filter docstatus Like "approved"


Report_report1.FilterOn = True
Report_Report1.Text1.Visible = False
DoCmd.Close acReport, "rptPage1", acSaveYes
DoCmd.OpenReport "report1", acViewPreview

I think that it is just the formatting on line 3 that is wrong (or maybe not)

Can anyone help?

 

[jkl0]

Try this for your filter.

Report_report1.Filter = "[docstatus] = 'approved'"

If wild card is needed,

Report_report1.Filter = "[docstatus] Like '*approved*'"

Hope this helps?

 

[Wiz47]

I have a form which is the main data entry for a document control db. Some of the documents have a status of approved and some not approved (also a few other possibilities).

If I open a report in design view I can filter the report with a line
docstatus like "approved".

I have added a button on the form which I want to do the above, i.e. display just the approved docs. Using previous help from this site I have added the following code.

stDocName = "Report1"
DoCmd.OpenReport stDocName, acViewDesign
Report_report1.Filter docstatus Like "approved"


Report_report1.FilterOn = True
Report_Report1.Text1.Visible = False
DoCmd.Close acReport, "rptPage1", acSaveYes
DoCmd.OpenReport "report1", acViewPreview

I think that it is just the formatting on line 3 that is wrong (or maybe not)

Can anyone help?

You could add a query as the recordsource for your report and it would list all the matches for the "approved" field in the Report

Select Your_db_Name.*

From Your_db_Name

WHERE (([Approved]=Forms!Your_Form_Name!Your_text_box_name Or Forms!Your_Form_Name!Your_text_box_name Is Null)=True)

 

[crescent-centre]

Thanks, will give both of these answers ago

 

[crescent-centre]

Another problem. The db is for a document review project. I have a form with doc ref and title and then a sub form which has date, action, status. Typical would be.
Doc 001 Title=Layout
1) 1 Jan 07; Doc recieved;
2) 1 Jan 07; ; Awaiting engineers review
3) 3 Jan 07; approval sheet issued ; approved

I can get a report which shows the complete history of each doc but would like a reoprt which shows just those which are still awaiting engineers review, hence previous question. However if I run a query on 'awaiting*' the report only shows line 2. I also need line 1 to show how long it has been in engineers in tray.

To clarify, I want a report which shows all information but only for those records with docstatus=awaiting

Ta

 

[Wiz47]

Another problem. The db is for a document review project. I have a form with doc ref and title and then a sub form which has date, action, status. Typical would be.
Doc 001 Title=Layout
1) 1 Jan 07; Doc recieved;
2) 1 Jan 07; ; Awaiting engineers review
3) 3 Jan 07; approval sheet issued ; approved

I can get a report which shows the complete history of each doc but would like a reoprt which shows just those which are still awaiting engineers review, hence previous question. However if I run a query on 'awaiting*' the report only shows line 2. I also need line 1 to show how long it has been in engineers in tray.

To clarify, I want a report which shows all information but only for those records with docstatus=awaiting

Ta

Just add the addtional parameter to the above query with an "AND" so the query would look something like ...

Select Your_db_Name.*

From Your_db_Name

WHERE (([Approved]=Forms!Your_Form_Name!Your_text_box_name Or Forms!Your_Form_Name!Your_text_box_name Is Null)=True) AND (([Awaiting]=Forms!Your_Form_Name!Your_text_box_name Or Forms!Your_Form_Name!Your_text_box_name Is Null)=True)

This should give you all approved docs that are awaiting a review.