Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2006
    Posts
    3

    Unanswered: Order By Problem

    I have a query

    Me.lstTrackList.RowSource = "SELECT DISTINCT cdbl(TrackID) as nTrackId, Activity, Subject, Submitter, Responder, " & _
    "Buno, UNS, Priority, OpenDate, CloseDate FROM tblTracks WHERE " & Me.cmbSearchBy.Column(1) & " = '" & Me.cmbSorted & "'"


    The TrackId in the table is "Text". In the query I convert it to double with the CDbl. This sorts the TrackID using nTrackID as if it was numbers in my listbox.
    instead of
    1
    10
    11
    2
    21
    23
    3

    it's sorted

    1
    2
    3
    10
    11
    21
    23


    When I add the "ORDER BY"

    Me.lstTrackList.RowSource = "SELECT DISTINCT cdbl(TrackID) as nTrackId, Activity, Subject, Submitter, Responder, " & _
    "Buno, UNS, Priority, OpenDate, CloseDate FROM tblTracks WHERE " & Me.cmbSearchBy.Column(1) & " = '" & Me.cmbSorted & "' ORDER By nTrackID"

    It no longer works. My listbox is empty. It does the same thing with my dates when I convert them.

    It works fine when I use the text fields. But, I am trying to get the number fields and date fields to sort properly.

    Thanks in advance.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I'm not sure you can use aliases in the ORDER BY clause, I could well be wrong. Give this a shot anyways:

    ORDER BY cdbl(TrackID)
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    how long is the text?
    if it is short enough, you can try

    ORDER BY right$('0000000' & textField, 7)

    for a 7-char-wide text field holding numeric characters.
    adjust to suit your field: 7 & the number of 0 need to agree

    izy

    LATER: my answer is brilliant, but maybe not for your question.
    memo - learn to read
    Last edited by izyrider; 01-05-06 at 15:48. Reason: mmmmmmmm
    currently using SS 2008R2

  4. #4
    Join Date
    Jan 2006
    Posts
    3
    Teddy,

    I tried that also. Didn't work. My list disappears.

    iztrider,

    My text items in the TrackID field will vary. It will also increase in size as records are added to the table.

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    It's funny that the listbox is flat-out empty when you use the order by option. Have you tried debugging your SQL statement and making sure it's actually returning records?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ignore my earlier - i was answering a question you didn't ask

    like Teddy, i also believe that you can't ORDER BY something you have derived as an AS alias in the current query ...you have to derive again for the sort.

    and if you are getting (unsorted) results with the AS
    ...you should be able to ORDER BY the same expression

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Jan 2006
    Posts
    3
    Well, the query works fine. Order by works fine except when I try to use an alias. You're probably right. Can't use an alias in the Order By clause. I will have to figure out a work around or settle.

    Thanks!

Posting Permissions

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