Results 1 to 14 of 14
  1. #1
    Join Date
    Jul 2017
    Posts
    7

    How to prevent Enter Parameter Value when Running a Query and a Report

    I am creating a Database for my job and I have created a summery query that individually takes the sum of two expenses (sum of advance and sum of liquidation) and then I created columns to find the totals of these two sums (total amount owed = sum of advance + sum of liquidation) by using Builder. I then created a report based off of this information.

    Every time I run this query and report, a Enter Parameter Value box appears. I click OK and the query shows the all the values but the report does not show the sum of advance and sum of liquidation values. I have even created a macro to open the query and the report and then I have SetWarnings No.

    I need help preventing the Enter Parameter Value box from appearing. Also, I do not know how to write code, so if it could be as simple and easy as possible that would be very appreciated.



    This is the code I have:

    DoCmd.OpenQuery "Payment Query", acViewNormal, acReadOnly
    DoCmd.OpenReport "Payment Report", acViewReport, "", "", acNormal
    DoCmd.SetWarnings False

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,515
    For starters, you don't need to open the query, just the report. What's the SQL of the query? It may be simpler to do the calculations on the report. Can you attach the db here?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Join Date
    Jul 2017
    Posts
    7
    Well, I pull the information from different tables that I have created in access. How do I attach the the db to just show the tables, query, and report?

  4. #4
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,515
    In Go Advanced area you'll find the Manage Attachments button.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    jeffroth1994 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    7
    It is telling me that it is too large to attach.

  6. #6
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,515
    Do a compact/repair and then zip it. Most are fine at that point.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    jeffroth1994 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    7
    Here you go.

    Peru 20171.zip

  8. #8
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,515
    I would eliminate the 2 fields that are just calculations and do that math on the report.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    When using fields for calculations you must use the name on the table/query. The problem is occurring in you Net Balance field where you are using made-up field names. Sum(Liquidation) etc. is the correct syntax.

    I would strongly recommend removing spaces from table and field names, they are very difficult to work with and will cause problems. Replace with underscore (_) or remove. Only users require spaces when data is displayed to them.

  10. #10
    jeffroth1994 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    7
    Now I get an error message that says " Cannot have aggregate function in GROUP BY clause (Sum([Liquidation Query]![Advance per Container]) + (Sum([Liquidation Query]![Liquidation])).

  11. #11
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    It would be "Expression", not group by.

  12. #12
    jeffroth1994 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    7
    Is that done under Builder? If it is, it only gives me Page Number, Total Pages, Page N of M, Current Date, and Current Date/Time as options.

  13. #13
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    If you are doing this in the totals query, then in the query on the group by line use Expression.

  14. #14
    jeffroth1994 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    7

    Thumbs up

    Oh!! Thank you!

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

Similar Threads

  1. Replies: 6
    Last Post: 11-08-2016, 07:49 AM
  2. Prevent query from running if false
    By keith701a in forum Queries
    Replies: 1
    Last Post: 08-09-2016, 03:17 PM
  3. ComboBox to enter parameter for report
    By MLangendorf in forum Reports
    Replies: 5
    Last Post: 07-21-2016, 02:04 PM
  4. Report 'Enter Parameter Value' Error
    By ndehhh in forum Programming
    Replies: 6
    Last Post: 10-18-2015, 05:18 PM
  5. ComboBox to Run Query and enter Parameter
    By tristangemus in forum Forms
    Replies: 13
    Last Post: 06-25-2013, 09:54 AM

Tags for this Thread

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