Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2002
    Posts
    11

    Unanswered: Order By 2 things on 1 field?

    A text field named SKU has in it strings like these; 1005693, 88800245, ABC-5846703, XYZ-23710. How can I sort or Order a Select query using the first character in SKU unless the fourth character is a dash, then I want to use the fifth character to sort?

    The query should return this;
    1005693
    XYZ-23710
    ABC-5846703
    88800245

    Not this;
    1005693
    88800245
    ABC-5846703
    XYZ-23710

  2. #2
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Hi there

    in your SQL select the field SKU twice


    So : SELECT SKU, SKU AS ORDERNO, ..........(other fields) FROM ect.


    But on the second SKU do the folowing :


    iif(instr(SKU,"-")>1,mid(SKU,5),SKU) AS OrderNo

    Then in your order by part order by OrderNo


    So youll have the folloing SQL:
    SELECT SKU, iif(instr(SKU,"-")>1,mid(SKU,5),SKU) AS OrderNo
    FROM [Table]
    ORDER BY iif(instr(SKU,"-")>1,mid(SKU,5),SKU)

    Go 4 it

  3. #3
    Join Date
    Dec 2002
    Posts
    11
    I tried this query and it works, 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
  •