Results 1 to 6 of 6
  1. #1
    geniass is offline Novice
    Windows 7 Access 2007
    Join Date
    Aug 2010
    Posts
    20

    Strange Behavior when Sorting

    Ok, I have another problem with queries now. This time, I have this query:


    Code:
    SELECT Items.[Name (Ingredients)], GetCost([Name (Ingredients)]) AS Temp, IIf([Temp]>4,[Temp]+([Temp]*0.2),[Temp]+([Temp]*0.25)) AS [Selling Price]
    FROM Items
    Now this works fine. The problem is that I want to sort it by Selling price:
    Code:
    SELECT Items.[Name (Ingredients)], GetCost([Name (Ingredients)]) AS Temp, IIf([Temp]>4,[Temp]+([Temp]*0.2),[Temp]+([Temp]*0.25)) AS [Selling Price]
    FROM Items
    ORDER BY IIf([Temp]>4,[Temp]+([Temp]*0.2),[Temp]+([Temp]*0.25));
    Now all of a sudden a message box pops up asks for a value for temp. This seems very strange, its like I ask it a question and it just asks me what the answer is?! This doesn't happen if I sort by the other 2 fields, but that's not what I want to do.

    Any help would be appreciated, I hope its as simple as the last one.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    It has to do with the order the clauses are evaluated, and the ORDER BY clause is evaluated before the SELECT clause, so the alias is unknown at that point. I would just apply a sort in the form or report the query is to be used in. You can also use a second query based on this one, or repeat the function call inside the ORDER BY clause (because of the number of times it would be called, probably the least efficient option).
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    geniass is offline Novice
    Windows 7 Access 2007
    Join Date
    Aug 2010
    Posts
    20
    Thanks for your reply, but I forgot to mention that I have a form based on this query, so as far as I know, using a sort without SQL won't apply to the form as well.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You should be able to use the Order By property of the form for sorting. If memory serves, you may need to do it in code in the form's load event:

    Me.OrderBy = "[Selling Price]"
    Me.OrderByOn = True

    I personally wouldn't use the spaces or symbols in my names. They're more trouble than they're worth in the long run.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    geniass is offline Novice
    Windows 7 Access 2007
    Join Date
    Aug 2010
    Posts
    20
    Ah, thank you this worked, but its strange that it didn't work in the query.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. A Little bit strange...
    By Maverick1501 in forum Access
    Replies: 0
    Last Post: 03-29-2010, 09:59 AM
  2. Replies: 6
    Last Post: 03-17-2010, 10:09 PM
  3. MsgBox strange/missing icon
    By AndrewAfresh in forum Access
    Replies: 6
    Last Post: 11-11-2009, 10:16 AM
  4. Replies: 3
    Last Post: 08-11-2009, 02:21 PM
  5. MS Access Enter Key Behavior
    By RAPSR in forum Programming
    Replies: 0
    Last Post: 12-23-2007, 02:31 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