Results 1 to 3 of 3
  1. #1
    drewj840 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Posts
    1

    Query-based report prompting for query parameters multiple times

    The query "Parts Sales By Location - Step 3" is built on the query "Parts Sales By Location - Step 2" and that, in turn, is built on the query "Monthly Summary By Category - Step 1" which has 2 parameters, Start Date and End Date. When I run the "Parts Sales By Location - Step 3" query, it prompts for the 2 parameters, as expected. But when I run the report "Parts Sales By Location Report" which is built upon the query "Parts Sales By Location - Step 3" I get the prompt 3 times. I cannot figure out why the prompt comes up 3 times.
    Attached Files Attached Files

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    If you get a prompt, then either:
    the query has a misspelled or non existent field,(so access thinks its a prompt)
    or
    the report has a GROUP sort that no longer exists.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Had a look at your db. Sorry to say you are doomed to experience a few serious problems and probably many more not so serious. At first glance, you have all sorts of special characters in your object names (even starting a name with a dollar sign) and plenty of spaces, tables with many duplicate rows, comma separated values in fields, many-to-many relationships between what should be one-to-many ([Dealers].[Corporation #] to [Raw Data].[Corp No]), query field references for what looks like non-existent fields (SumOfDura-lift: Sum([Monthly Summary By Category - Step 1].[Dura-lift]) (I see no Dura-Lift field in that query) AND every field in the detail section of your report has an invalid data reference. The last issue is probably why your report wants the date 3x. Each query that calls an underlying query uses the same field name, which may be why Access cannot resolve the field reference in your report. It says it's an invalid reference even though the field exists (but 3 times, once at each query level) and it does not show up in the list of available fields for the control (based on the report's data source). When I tried to use the builder to see how/if it would find the field, the db crashed. So if this is an issue for the company data field, it certainly could be for the date being supplied.

    With all due respect, you have some serious design issues.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 07-26-2016, 02:12 PM
  2. Query running multiple times
    By Dida in forum Access
    Replies: 1
    Last Post: 08-28-2015, 10:13 AM
  3. Run query multiple times on different tables
    By dumbledown in forum Queries
    Replies: 2
    Last Post: 03-14-2012, 05:39 AM
  4. parameter prompting multiple times
    By sandyg in forum Access
    Replies: 1
    Last Post: 08-15-2011, 10:10 AM
  5. Replies: 1
    Last Post: 07-12-2010, 12:00 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums