Filtering a Report

[NDD]

I have a report based on a query.
There is a field in my personnel table and the query: [Field] with values from my lookup table and has three choices: 1-Inspections 2-Administrative 3-Safety

I have a report and would like to filter based on [Field]

I can filter by putting [Field]=Inspection on the Filter On Load, then typing 1 into the parameter box. And it works.

I cannot however choose more than one of the three choices and I have to type the number in. Fine for me but it won't be for other users.

I would rather not ask the question of my query, as it is used for more than one report. I would also like to avoid having seperate queries for all these reports.

In other words, I would like to filter the report, not the query.

I need to go to VBA school.

In the meantime, any suggestions?

 

[pr2-eugin]

A Form Based Query Based Report is the way to go I would think.....

 

[NDD]

Thanks,
Can you expound while I research?

 

[NDD]

http://www.techrepublic.com/blog/microsoft-office/run-a-parameter-query-within-an-access-form/

I did this and it's working great for the query. How can I turn this query into a report without showing the query after I run the parameter?

I have a form called "Report Center" with all my reports on it.

Well, it doesn't work on the Navigation Form because the report center is a subfor on the Navigation Form. Disappointing, but I'm learning new stuff.

 

[NDD]

I made a parameter query and am using a form to put the data and run the query per these instructions
http://ms-access-tips.blogspot.com/2011/06/using-unbound-form-to-obtain-query.html

It works good.

Now what I need to do is get my parameter query form to pop up when the auditor clicks on the report. Can somebody help me do this?

 

[NDD]

I have the parameter query working to open the form, just changed the event to open the form instead of the query. The paramter query form works good when I open it by itself, put when I put it in my navigation form, it doesn't work. Any suggestions? Thanks.

Edit: Fixed. Have to reference the navigation form and then the subform in the Navigation Form.

 

[GinaWhipp]

That is correct because of the parameters you set up it would be different if opening within the Navigation Form. Are you always going to open from the Navigation Form?

 

[NDD]

That is correct because of the parameters you set up it would be different if opening within the Navigation Form. Are you always going to open from the Navigation Form?

Thanks Gina. Yes, pretty much. I'm playing with it now (and having a blast). I really like it when things start working the way I see them in my head.

 

[GinaWhipp]

Have fun! Post back if you need additional help...

 

[NDD]

I need a little more help with this.
I have this in my query:
[Forms]![Main]![NavigationSubform].[Subform]![Status]
and it works fine as long as the status is put in the parameter subform of the navigation form.

What I would like to do is put the parameter subform in a navigation subform titled Report Center. Which I did.

I tried this:
[Forms]![Main]![NavigationSubform]![Report Center]![Subform]![General Safety Reports]![Status]
but it isn't returning any results.

I can't quite figure out how to make the query look at the General Safety Reports parameter subform in the Report Center form in the Main Navigation form.

I'm close, I can feel it.

When I put:
[Forms]![Main]![NavigationSubform]![Subform]![Status]
in the query, I get the pop up asking me for the parameter and it works. It just isn't reading the parameter from the General Safety Reports Subform on the Report Center Navigation subform.

 

[GinaWhipp]

Hmm, this should help...
http://access.mvps.org/access/forms/frm0031.htm

 

[NDD]

So:
Where I put the number for Status is an unbound text box "Status"
It is on Subform "General Safety Reports"
Which is on Navigation Subform "Report Center"
Which is on Navigation Form "Main"

So I need: Me!.Main.Report Center.General Safety Reports.Status

?

 

[GinaWhipp]

Hmm, don't want to say *Yes* without seeing it so, I have a tried and true way of figuring out how to refer to a control when nothing else works (or I am unsure)...

1. Open Form in Design View
2. Put a Text Box on the form you want to put the function on
3. In the Properties Window for the Text Box go to the Control Source and click the Elipse button [...]
4. In the window that opens navigate to the form you want to refer to
5. Once there in the center window double click on <Form> and the path will show in the window above
6. Copy what you see in the above window and cancel out till back at the Properties Window.
7. Now, go paste that path in your function...

 

[NDD]

Forms![Main]![NavigationSubform]! Forms![Report Center]![General Safety Reports] ![Status]

Is what I ended up copying

I got an error message:
The expression you entered has an invalid dot or ! or invalid parenthesis
You may have entered an invalid identifyer or typed parenthesis following the null constant.

 

[NDD]

[Forms]![Main]![NavigationSubform].[Form]![General Safety Reports]![Status]

Worked on the first one.

 

[GinaWhipp]

I see some extra spaces in the there, like right before ![Status]. Tighten those up.

 

[NDD]

Yes ma'am. I've got them both working now. Is it Gina or Genius?

 

[GinaWhipp]

Hmm, seen my web site, that would be Diva!

Glad that is working!

 

[NDD]

One last thing to do and I can take a break for a month or two. Have to research putting user names and passwords.

I shall peruse your website.

 

[GinaWhipp]

Nope, you don't have to look to far for that, have a look here...

http://www.access-diva.com/f7.html
http://www.access-diva.com/f10.html

There are also additonal Links on first link to other sites.