Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201

    Unanswered: Sorting problem in queries and VBA

    Hullo again, Colleagues All,

    I have a funny little problem in sorting query results upon which I could do with some advice from you clever guys out there.

    First, some context. I am modelling functions (parties, dinners, etc. not VBA !) and each function has several lists of components (meats, salads, vegetables, etc.) recording customer selections and stored in a series of subtables (which I call detail tables). Each type of component has a corresponding lookup table which populates list-boxes for selection and also holds various parameters for computing quantities to order and so forth. When I run the ordering program, I need to join the corresponding lookup tables and detail tables. So far, so good. The quantities required are scaled to the number of guests, but are supplied in packages of a particular size. The components (salads, say) are ranked in order of convenience specified by the client, so that the computations will favour the most convenient ones when the amount required is met. Hence, our list of salads must be sorted in order of ranking and here lies the problem.
    The ranking is simply a number (any old number, so long as it fits between the hext highest and next lowest) in the lookup table and when I open the lookup table or have a query on only the lookup table, the records sort on ranking in numerical order as expected.
    When these records feature in VBA SQL queries or in multi-table queries, something funny happens. The sorting is done in character order. In other words, instead of 1,2, 3, 11, 12, 13 . . ., I am getting 1, 11, 12, 13, 2, 21, 22 and so on. To keep the client's system running, I have assigned arbitrary rankings to respond to this situation, but I need to know how to return the sorting process to numerical.
    Here is the research I have done so far. I originally used byte sizing for the ranking to take up less space and it occurred to me that the sorter may be interpreting them as characters. I therefore changed them to integer. No joy. My next try was to create a new table with the field already set to integer and then carry out an append query from one table to the other. Still no joy.
    In both cases, I relinked the backend to the three front-ends I am using, in case the old specifications were cached somehow in the links.
    Has anyone encountered this problem and is there anything I can do about it. If anyone has a solution, the psychiatric services in Australia will be eternally grateful, as I am certainly heading down that track at the moment !

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What's the datatype of the sorting fields? If it's a number stored as text, it's going to sort like text!
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    change the data type to numeric and all will be well
    unless you have a very very good reason choose the datatype that most closely matches the data contianed

    eg for...
    dates choose a datetime datatype
    numbers a numeric data type
    ....part of the No Shit Sherlock school of DB design

    the reason you are getting your sort order banjaxed is the data type is text so 1,10,100,101,11,110,111 etc is the correct sort order.

    now you may be able to kludge the current design at great perfomrance cost by using order cint(mytextcolumn). but that is such a nasty kludgy workaround its not worth using.... fix the basic design problem all become easier

    the potential problem I forsee is however where you have an alphanumeric group eg 1, 1.1, 1.1.a and so on... you will have to use a text cilumn to store that. but all is not lost, when capturing the numbers put in either leading zeros or leading spaces

    say you think the maximum number is going to be 9999, if you pad with leading zeros then you store: 0001,0002,0100,0010
    leading spaces is easier on the human eye, especially if you are using a proportional font.. the downside is that when you search for the item you need to make sure you put in those leading spaces or zeros.

    make life easier on yourself, and your users.. change the datatype to numeric (either integer or similar)
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201
    Read my lips (or rather my post!). The type was numeric and the precision was byte, later changed to integer (in case byte was interpreted as char).

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Can you post the SQL?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201
    I attach a small database with the relevant bits which will demonstrate the problem. It also includes a snip from a VBA module which shows how the data is meant to be used.
    Attached Files Attached Files

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Downloaded. There are four queries, none have an order by clause.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Easy really.

    PMRanking is the field you want to sort by and it is being coerced into text because of Nz().

    You can tell it's being interpreted as text because it's left aligned; values are always right aligned in a query.

    Either don't use the Nz() function or coerce it back to a number:

    PMRanking: CLng(Nz([SaladLookup].[Ranking],0))
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201
    Very observant, StarTrekker! You have actually highlighted a trap into which it is very easy to fall. (Don't you love that grammar ?!). Functions like Nz() are often used by rote, especially in VBA, simply to avoid a break because of an untested null value. When equated to a declared variable, the conversion takes place automatically and it is easy to overlook the steps in the process. In this case, the ordering took place before the conversion.
    Actually, in the light of this, I am rethinking the way in which I manage my model, which is designed to drive database operations as far as possible from metadata stored in lookup tables. This is because the client's business is very dynamic with lots of specials and seasonal changes in product offerings. I think I will use the lookup table editors to force default values for every parameter (which will be visible to the product designers and make them think things through properly) and reserve Nz() testing only for front-of-house user-entered data. This will create a more functional link between the VBA code and the database design.
    In answer to your remark, PootleFlump, the queries were designed for VBA reference and the ORDER BY clauses were omitted in case the occasional unions between queries (with their own ORDER BY clauses) created a conflict or affected performance. You can test the sorting in the materials I sent you by right-clicking on the column after opening the query.
    Many thanks for your responses, colleagues all. As always, they have generated food for constructive thought.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Jim, just to point out - if there is no ORDER BY clause, you can not guarentee the sort order.

    Also note, if you had put
    Code:
    ORDER BY PMRanking
    (note the absence of the Nz function here)
    Then the results would be sorted as expected.
    George
    Home | Blog

  11. #11
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Very observant, StarTrekker! You have actually highlighted a trap into which it is very easy to fall.
    Thanks, I have my moments

    And agreed.... Nz is definitely over-used by many without knowledge of this "trap". Most of the time, the ORDER BY clause corrects this kind of thing, as pointed out by Georgev, but not always.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    To me, this is still the key point.
    Quote Originally Posted by pootle flump
    ...none have an order by clause.
    You can never guarentee the order of a resultset without an ORDER BY clause.
    Quote Originally Posted by Brett Kaiser
    The physical order of data in a database has no meaning
    George
    Home | Blog

  13. #13
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    There are sort buttons and menu commands that will sort a recordset that has no ORDER BY clause. I've never known them NOT to work.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by StarTrekker
    There are sort buttons and menu commands that will sort a recordset that has no ORDER BY clause. I've never known them NOT to work.
    there is one case where order by doesnt' work....
    if the order by is part of a query for a report, the order by is not guaranteed two work because (I suspect) the report generator adds its own sorting and grouping..... at least thats what I think it does.... it certainly used to cause problems on older versions of Access
    I'd rather be riding on the Tiger 800 or the Norton

  15. #15
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yes, sorting and grouping options in a report's design will override any sorting done at the query level. This is true for all versions of Access.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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