Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297

    Unanswered: SQL - ORDER BY number in string

    Code:
    THIS QUESTION HAS BEEN SOLVED... THE ISSUE I HAD WAS THAT THE [Product Code] DIDN'T ALWAYS CONTAIN A '-' IN IT, WHICH WAS CAUSING THE INSTR STATEMENT TO RETURN 'FALSE', WHICH OBVIOUSLY CAN'T BE CAST TO A LONG INTEGER!
    
    !!PLEASE IGNORE!!
    Hi guys,

    Pretty stuck with this, and haven't got time to pull my hair out dealing with Microsoft's limited implementation of SQL in Access...

    Basically I have exactly the same problem that can be seen:

    mysql - Sorting string column containing numbers in SQL? - Stack Overflow


    The only difference is, that I'm working in MS Access.

    I've attempted to port the solution given over to Access SQL, which I've worked out to give me:

    Code:
    ORDER BY CLNG(MID([Product Code], INSTR(1, [Product Code], '-')+1))
    But this gives me a:

    Code:
    Run-time error '3464':
    
    Data type mismatch in criteria expression.
    Obviously not the most helpful error, as it doesn't tell me which part of the command is having the data mismatch (thanks VBA debugger!).


    Anyone got any ideas? Or can give me a working solution would be greatly appreciated!


    Oh, the data looks like:

    Code:
    ABCDEF-1
    ABCDEF-10
    ABCDEF-2
    ABCDEF-3
    When:

    Code:
    ORDER BY [Product Code]
    Is used.


    Thanks!
    Last edited by kez1304; 05-16-13 at 11:22. Reason: [SOLVED]
    Looking for the perfect beer...

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Your query should yield the expected result (see attachment). Is it possible that the Product Code does not always follow the pattern given in your sample data? Can the Product Code be Null for some rows?
    Attached Thumbnails Attached Thumbnails Query.jpg  
    Have a nice day!

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the limitation ins't in the SQL its in your design
    store numbers in numeric datatypes, date or times in datetime, and so on.
    if you know the left most 6 character are always alphabetic and you don't want the hyphen then
    abs(clng(right(mycolumn, len(mycolumn)-6)) will do the job
    for saftey ytou might want to trim leading and trailing spaces
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jan 2005
    Posts
    146
    It looks like it is caused by CLng(Null). You can't convert on Null. There must be a [Product Code] that is returning Null.

Posting Permissions

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