1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access 2007 - Report from Query with Parameter

Discussion in 'Business Applications' started by Wayne0928, Nov 18, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. Wayne0928

    Wayne0928 Thread Starter

    Joined:
    Nov 11, 2010
    Messages:
    38
    I have a report I run based on a query that works perfectly. I'm managing event registration for an association of school student councils. The report is a separate page for each school that summarizes the information I have for that school relative to its participation, i.e. school name, address, who is coming, how much they owe, etc.

    It works fine when I want a page for all of the schools, but sometimes I just want a single school...not all of them. I copied my original query and set up a parameter in the copy [What school?] and it selects the school I want. However, when I run a copy of my original report I made changing the source to my new query, when I open the report, the parameter box opens, I put in the school name I want, and the box comes up again, and eventually my report opens with no data in it.

    Help? I don't do VBA, but I get around Access pretty well.
     
  2. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,334
    Put an * in the criteria prompt if you want all records. There are probably a thousand different ways to tackle this, but this should work.
     
  3. Wayne0928

    Wayne0928 Thread Starter

    Joined:
    Nov 11, 2010
    Messages:
    38
    I can get the one with all of the schools and their data. That works fine. My problem is getting just one school and its data.
     
  4. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,334
    What field did you put the criteria [What School] into? It would have to be the school name field in your query.
     
  5. Wayne0928

    Wayne0928 Thread Starter

    Joined:
    Nov 11, 2010
    Messages:
    38
    Yes. That's the field I put it in. I just discovered something though. When I open the report, the parameter dialog box appears asking "What School?" but when I put in the school I want, it comes up again. I tried entering the school name each time it came up and discovered that after 3 times the right report shows up.

    In other words, it actually does do what I want it to do, however it makes me work for it.:) I'm sure I shouldn't have to to deal with a dialog box asking me the same thing 3 times to get there. Any ideas?
     
  6. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,334
    If it asks for the criteria more than once it may be because you are using the criteria across several different nested queries.
     
  7. Wayne0928

    Wayne0928 Thread Starter

    Joined:
    Nov 11, 2010
    Messages:
    38
    Yes, the query I'm pulling from actually pulls from 4 other queries. Is there anything I can do about that or is that just the consequence of the sloppy way I've designed my database?
     
  8. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,334
    Put it in the initial query and see if it works. If it does the same thing put int in the final query.

    While in your query in design view go to query parameters and define the datatype to text.
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1027386

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice