Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2009
    Posts
    8

    order by - string and numeric

    Hi there!



    i want to sort a table by a field that could contain both numerical or string data. in case it's numerical i found out i can use ORDER BY val (field) but if it's a string it gives an error of course. i can't leave it as ORDER BY field either cause then it sorts numbers like: 1,11,12,2,25 etc
    is there any way i can sort the data wether they are strings or numeric?

    thanks

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    So it is a Text field that sometimes has just text numbers in it?

  3. #3
    Join Date
    Mar 2009
    Posts
    8
    yes, it can be numbers or text

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    Are the numbers always integers (no decimal) and how large a number will you have in the field?

  5. #5
    Join Date
    Mar 2009
    Posts
    8
    yes, they're always integer and not large, always 2 digits. i was thinking about putting zeros in front of one-digit numbers but i don't know how to do that either..

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    In the query create another field to sort on and set it to:
    SortIt: If IsNumeric([TextField],Format([TextField],"000"),[TextField])
    ...using your [TextField] name of course.

  7. #7
    Join Date
    Mar 2009
    Posts
    8
    i don't know how to implement this into my query, i only know how to make simple ones. that's my query:

    SQL="SELECT TOP 150 * FROM datakod1 where eidos='"&eidos&"' AND aa like '"&aa&"%' AND arthro Like '"&arthro&"%' ORDER BY aa, arthro,paragr "

    the field "arthro" is the one containing both numeric and string types.



  8. #8
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    The wildcard character in Jet (Access) is an asterisk "*" rather than a percent sign "%" so you will want to replace them. Paste what you have in the SQL view of a new query. Then switch to Query Design view and paste the following in the Field line of an open cell:
    SortIt: If IsNumeric([arthro],Format([arthro],"000"),[arthro])
    Then you will need to change the OrderBy clause to use the new field instead of the arthro field.

  9. #9
    Join Date
    Mar 2009
    Posts
    8
    sorry again but i'm doing it via asp code, not directly on MS Access, still don't know how to do that..

  10. #10
    Join Date
    Mar 2009
    Posts
    8
    well, i tried to do that in access but it didn't work thanks anyway

  11. #11
    thhui is offline Competent Performer
    Windows XP Access 2002 (version 10.0)
    Join Date
    Feb 2009
    Posts
    235
    The correct synatax of the ordering field is :-

    Code:
    iif(IsNumeric([arthro], Format([arthro], "000"), [arthro]))

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Numeric value out of range (null)
    By PPCMIS2009 in forum Queries
    Replies: 0
    Last Post: 02-03-2009, 11:01 AM
  2. Chart Order
    By protean_being in forum Reports
    Replies: 0
    Last Post: 06-19-2008, 09:26 AM
  3. Replies: 0
    Last Post: 12-11-2006, 04:55 PM
  4. How to report data in fical year order
    By gemini2 in forum Reports
    Replies: 1
    Last Post: 05-27-2006, 06:26 AM
  5. CallTicketID populated to Order Form
    By EisBlade in forum Forms
    Replies: 0
    Last Post: 04-03-2006, 10:50 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums