Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Mar 2004
    Posts
    10

    Question Unanswered: sorting numbers with a - in it

    Hi, is there a way to sort in order numbers with -'s in a field (ie 123-2) ? if i pick number in the design mode of the table it wont allow -'s or if i pick memo/text it doesnt put them in order but allows -'s. Looking for it to be 123-1 then 123-2 then 124 ect some entrys will have -'s others wont.

    Thanks
    Brian

  2. #2
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    Have you tried using the sort function in your query? Sort ascending on the text field.

    Let me know if that works.

    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  3. #3
    Join Date
    Mar 2004
    Posts
    10
    well in the form/table its not showing in order - not using a query


    **edited* i just tried it in query - same thing

    it wants to sorta liek 100/1000/200/2000 ect
    Last edited by slothy; 05-11-04 at 14:02.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    a simple text sort will return
    123-1
    123-2
    124
    125-1
    125-10
    125-11
    125-2
    125-3
    etc
    ...which is probably not what you want.

    if it is always three chars left of the '-' then you could sort on
    left$([theFieldName], 3)
    and then
    cint(mid$([theFieldName], 4)) MUCH LATER, should be 5

    i.e. you put these two calculated fields next to each other in a query and sort ascending on both.

    if you are not certain to have three chars left of '-' then you could use cint(left$()) using instr() to find the '-' for the left$().

    in truth, you have perhaps done yourself a disservice by storing the data this way. if you had order/suborder fields as numbers, you could easily reform the 999-99 whenever you needed it and handle sorting "naturally".

    izy
    Last edited by izyrider; 05-12-04 at 15:16.
    currently using SS 2008R2

  5. #5
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557

    Condsider a Query

    You may want to consider running it through a select query. That way you can sort it. Or you can open the table and click the header of the column (to select all of it) and then click the sort ascending button. (Should be on your tool bar).

    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  6. #6
    Join Date
    Mar 2004
    Posts
    10
    Quote Originally Posted by JSThePatriot
    You may want to consider running it through a select query. That way you can sort it. Or you can open the table and click the header of the column (to select all of it) and then click the sort ascending button. (Should be on your tool bar).

    JS


    Hi tried that, sorts the same way as it did in the table...

  7. #7
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    How is it sorting in the table? 123, 124, ... , 123-1, 123-2, 124-1,...?

    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  8. #8
    Join Date
    Mar 2004
    Posts
    10
    well here is the field works -

    right now we are at job # 2560, but sometimes if there was a addition to the job we add a -1,-2 after the job# (ie 2560-1) so there are some without the dashes. i wanted to try and keep the -'s there for the sake of people being able to understand it, but with me being super new to access might not help any.

    right now its sorts 100's, then 1000's then 200's then 2000's and so on
    trying to have it sort from from 100 to recent..

  9. #9
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    Oh okay... I will see what I can get firgured out.

    I hope I can help,
    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    please read what i said.

    izy
    currently using SS 2008R2

  11. #11
    Join Date
    Mar 2004
    Posts
    10
    Quote Originally Posted by izyrider
    please read what i said.

    izy
    trying your idea right now - thanks

  12. #12
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    (i'm not looking at A for this - hope there are no errors)

    since you talk of 100, 1000, 10000 it is clear that you don't know the position of the '-' in advance.

    instr([theField], "-", 1) should return the position of the '-'

    so your two calculated query fields are:

    cint(left$([thefield], instr([thefield], "-", 1) -1))
    cint(mid$([theField], instr([thefield], "-", 1)+1))

    that should sort the sort!

    izy
    currently using SS 2008R2

  13. #13
    Join Date
    Mar 2004
    Posts
    10
    Quote Originally Posted by izyrider
    (i'm not looking at A for this - hope there are no errors)

    since you talk of 100, 1000, 10000 it is clear that you don't know the position of the '-' in advance.

    instr([theField], "-", 1) should return the position of the '-'

    so your two calculated query fields are:

    cint(left$([thefield], instr([thefield], "-", 1) -1))
    cint(mid$([theField], instr([thefield], "-", 1)+1))

    that should sort the sort!

    izy

    I sent u a Pm

  14. #14
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    can't uncover how to GIF in p-mail, so...
    currently using SS 2008R2

  15. #15
    Join Date
    Mar 2004
    Posts
    10
    Hi, okay did that but now i get a Invalid procedure call when i try to open the query


    SORT: CInt(Left$([JOB_NUMBER],InStr([JOB_NUMBER],"-",1)-1))
    SORT1: CInt(Mid$([JOB_NUMBER],InStr([JOB_NUMBER],"-",1)+1))


    job_number firld set for no sort

Posting Permissions

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