Results 1 to 7 of 7
  1. #1
    DefinitelyMaybe is offline Advanced Beginner
    Windows 10 Access 2002
    Join Date
    Dec 2020
    Posts
    54

    Query prompting for Parameter Value, Not sure why

    Good morning, afternoon or evening,

    I have query that is prompting me for a parameter value on an expression where I am summing and returning zero if null----- FieldName: Sum(Nz([AdjustmentAmt],0)). This AdjustmentAmt field is only in one of the two joined tables. No misspellings... and the query works if you leave blank and click ok. Not the end of the world just annoying. Any thoughts as to why this is happening and how to fix?

    Thanks,



    Eric

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    1,205
    Why not show the whole SQL?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?search_query=debug+access+vba



  3. #3
    DefinitelyMaybe is offline Advanced Beginner
    Windows 10 Access 2002
    Join Date
    Dec 2020
    Posts
    54
    Hi Welshgasman,

    I'd be glad to share anything that would help:

    SELECT Tbl_Budget.BudgetYear, Tbl_Budget.CreateDate, Tbl_Budget.BU, Tbl_Budget.CapitalID, Tbl_Budget.ProjectName, Tbl_Budget.ProjectOwner, Tbl_Budget.ApprovedBudget, Sum(Nz([Tbl_Adjustments]![AdjustmentAmt],0)) AS SumAdj, [ApprovedBudget]+[SumAdj] AS Forecast INTO Tbl_Make_RevisedBudget
    FROM Tbl_Budget LEFT JOIN Tbl_Adjustments ON Tbl_Budget.CapitalID = Tbl_Adjustments.CapitalID
    GROUP BY Tbl_Budget.BudgetYear, Tbl_Budget.CreateDate, Tbl_Budget.BU, Tbl_Budget.CapitalID, Tbl_Budget.ProjectName, Tbl_Budget.ProjectOwner, Tbl_Budget.ApprovedBudget, [ApprovedBudget]+[SumAdj];

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    2,576
    What happens if you change the Totals row from Group By to Expression for the Forecast field ([ApprovedBudget]+[SumAdj])?

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    1,205
    And it is prompting you for SumAdj?
    I do not think you can use calculated values in the same query?
    Replace it with how it is derived ie sum()

    HTH
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?search_query=debug+access+vba



  6. #6
    DefinitelyMaybe is offline Advanced Beginner
    Windows 10 Access 2002
    Join Date
    Dec 2020
    Posts
    54
    Hey Vlad... You solved it. This one was much easier than the last!

    Thanks for trying to help HTH...

    Thanks!

    Eric

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    2,576

    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. Replies: 5
    Last Post: 11-21-2018, 04:42 PM
  2. query prompting for a value that is already defined
    By willmafingerdo in forum Queries
    Replies: 8
    Last Post: 07-18-2016, 05:58 AM
  3. Join query is prompting for parameter?
    By tb1150 in forum Access
    Replies: 11
    Last Post: 06-19-2015, 08:36 AM
  4. Replies: 3
    Last Post: 12-28-2013, 09:13 AM
  5. parameter prompting multiple times
    By sandyg in forum Access
    Replies: 1
    Last Post: 08-15-2011, 10:10 AM

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