Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2011
    Posts
    2

    Question Unanswered: Access Linked Query Order By Problem

    Greetings,

    I was asked to migrate an Access db to MS SQL using the same Access front end. I have migrated all of the data and created an MS Access project frontend from their old frontend. Everything is working except one form where I cannot get the sort order right.

    The table has 3 primary key fields

    Part_ID nvarchar(50)
    Part_Num nvarchar(50)
    length int

    The problem is that Part_Num contains numbers separated by '-' such as

    1-1
    1-2
    1-10

    SQL sorts the field as

    1-1
    1-10
    1-2

    I have written a view in SQL that pulls the two values out as integers of Part_Num and sorts them correctly.

    CASE WHEN CHARINDEX('-', [Part_Num]) = 0 THEN NULL
    ELSE CAST(LEFT([Part_Num], CHARINDEX('-', [Part_Num]) - 1) AS int) END AS Sort_1,

    CASE WHEN CHARINDEX('-', [Part_Num]) = 0 THEN NULL
    ELSE CAST(RIGHT([Part_Num], LEN([Part_Num]) - CHARINDEX('-', [Part_Num])) AS int) END AS Sort_2

    ORDER BY Part_ID, Sort_1, Sort_2, Length

    This view displays the values as I want to see them.

    1-1
    1-2
    1-10

    When I look at the linked query in Access, the are sorted incorrectly

    1-1
    1-10
    1-2

    I have tried using the Order By property in the Form design with the Order By On Load set to yes.

    I'm at a loss as to why Access insists on sorting by the Primary Keys when I have defined a query that explicitly sorts differently.

    Any suggestion would be appreciated.

    Thanks

    Keith

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Why don't you reproduce the sorting mechanism you implemented in the SQL server in the Access query?
    Have a nice day!

  3. #3
    Join Date
    Mar 2011
    Posts
    2
    It is an Access Project where all views and stored procedures are stored in the SQL Server

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Sorry, I did not paid attention that you wrote it was an Access project (adp).
    Have a nice day!

Posting Permissions

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