Results 1 to 6 of 6
  1. #1
    wwconslt is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2017
    Posts
    9

    Sorting on Expression results in Prompt Box asking for Parameter

    Hello Everyone,



    Access 2016

    I'm trying to sort on an expression in query design view. The expression works fine when I run the query. As soon as I click Ascending or Descending in design view, I get prompted for a Parameter Value.

    For example, I'm trying to sort on this: expr_Gross_Profit_Margin: ([expr_Gross_Sales]-[COGS])/[expr_Gross_Sales]

    But I get the box "Enter Parameter Value for expr_Gross Sales"

    expr_Gross Sales is defined as: [Units_Sold]*[Sale_Price]

    Thank you!

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    AFAIK it's because exp_Gross Sales isn't known until the query runs. I'd try either just entering a value or just hit OK rather than Cancel (that's an assumption) or edit it in sql view and save.

    As soon as I click Ascending or Descending in design view, I get prompted
    EDIT - out of curiosity I tried to replicate your issue and cannot. I only get a prompt when I run it, not in design view. If you're not saying that, then I think you will have to use a function to return a value to the query. Not sure if that will solve it or not.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    wwconslt is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2017
    Posts
    9
    Sorry I wasn't clear. It happens when I run the query from design view.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    duplicate post
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I don't think you can do what you're trying to do since at run time, an expression needs to be evaluated in order to feed another one, and the query plan is going to try to evaluate all expressions before it runs. However it cannot in your case. As I mentioned, I think you'll have to either call a function from your query or join another query into yours - one that provides the result of an expression. I would have to try one or both to see if either worked. Can you copy, compact/repair the copy, zip it and post here so we can see your query?

    EDIT - OK, so continuing to play with what I have I could get it to run where expr1 is a query field*2 and expr2 is
    expr2: [expr1]-1. I think you either need to ensure that every reference in the expressions is correctly spelled as that is often the cause. Or as mentioned, post your db copy.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    wwconslt is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2017
    Posts
    9
    Thank you. I created another query that references the original query and it seems to work!

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

Similar Threads

  1. Replies: 2
    Last Post: 07-13-2019, 02:45 PM
  2. Parameter value prompt
    By templeowls in forum Queries
    Replies: 1
    Last Post: 02-27-2019, 04:17 PM
  3. Parameter Prompt When Accessing Report
    By MdHaziq in forum Reports
    Replies: 3
    Last Post: 01-10-2018, 10:21 PM
  4. Report Parameter Prompt
    By leamas in forum Access
    Replies: 7
    Last Post: 05-31-2012, 02:07 PM
  5. Get parameter prompt
    By yawalias in forum Queries
    Replies: 1
    Last Post: 12-22-2011, 09:25 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