Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jun 2005
    Location
    Atlanta, Georgia, USA
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Ascending order problem (2003)

    I have a query where I compute Determination Due Date and the other field hours left take the Determination Due Date and finds the hours difference between the 2 dates. My problem is when I run the query it can't descend or descend based on hours left. I believe the problem is that the field becomes text after the calculation and therefore has problems sorting. How can I remedy this?


    DeterminationDueDate: IIf(IsNull([GWHtime]),"",IIf([Addinfo]="n",DateAdd("h",[URACTAT],[GWHtime]),DateAdd("h",[Left],[infofromreq]))

    Hoursleft: IIf(IsNull([Datelettersent]),DateDiff("h",Now(),[DeterminationDueDate]),"")

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Ascending order problem (2003)

    The result is text because you return an empty string "" if certain conditions are met. Try using Null instead of "".

  3. #3
    2 Star Lounger
    Join Date
    Jun 2005
    Location
    Atlanta, Georgia, USA
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Ascending order problem (2003)

    I added null to no avail. The query runs if I don't sort Hoursleft but I need it sorted to alert users of tasks that need to be completed within certain time frames. When I sort, I get enter parameter value:determinationduedate so I click ok and the query runs but it still doesn't sort ascending. Any hints?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Ascending order problem (2003)

    One possibility is to leave the query you have now unsorted, and to create a new query based on it, and specify the sort order there. This extra "level" should enable Access to sort the records.

    Another possibility is to inccorporate the definition of DeterminationDueDate in the definition of HoursLeft:

    Hoursleft: IIf(IsNull([Datelettersent]),DateDiff("h",Now(),IIf(IsNull([GWHtime]),Null,IIf([Addinfo]="n",DateAdd("h",[URACTAT],[GWHtime]),DateAdd("h",[Left],[infofromreq]))),Null)

  5. #5
    2 Star Lounger
    Join Date
    Jun 2005
    Location
    Atlanta, Georgia, USA
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Ascending order problem (2003)

    Thanks . That works.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •