Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2016
    Posts
    14

    Unanswered: How to build a very very customized "Criteria" for a query?

    Hi,

    I want a query to sort my records in the order shown below:

    5
    85
    85b.2
    97
    107
    107a
    107a.1
    107a.2
    108
    108b
    108b.1
    108b.3
    1020
    2340a
    A2
    CS40
    S108b
    S108b.1
    S108b.3
    S456


    This is not a fixed list, just an example.

    Thank you in advance!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you need to structure your data if you want it sorting. how you structure that data is upto you.
    the basic problem is that you have alphanumeric data so you need to set up either store the alpha and numeric components separately of impose a formatting

    the key problem's are:-
    stored as text 1,2,3,4,5,6,7,8,9,10,11
    comes out as 1,10,11,2,3,4,5,6,7,8,9
    ..the solution either pad the numbers so they are int he right sequence. that padding can be 0 or a space

    1.1.1
    1.2.1
    1.10.... will also have this problem
    padding with sufficient 0 or spaces gets round the problem

    001,002,003,004,005,006,007,008,009,010,011
    or
    1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11
    you can always strip out leading zero's from a value

    or store the components separately, but that gets very messy and very prone to breaking if you dont' control the numbering schema

    the key is making certain that your paddimg is at least as big as the expect maximum value. prudence would always suggest 'bung' on at least 1 character more than the current maximum, so if the maximum range you expect is 1-99 then use a minimum of 3 characters to allow for outliers. ie store as 001,002...099. butter cynics like me would probably pad with 2 charatcers 0001..0099 (or ' 1', ' 2'.... ' 99' so the system can handle numbers upto 9999

    leading spaces can be removed used ltrim when using the data
    but make certain whatever padding technique yoiu do decide to use make certain its plenty big enoiughg for the current and projected use. renumbering is a problem on such unstructured data. it can be be doen but its easy to screw up.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2016
    Posts
    14
    Thank you for your reply

    I considered adding couple of 0, but the problem with the records starting with a letter remains.
    I'm not very sure how to use the ltrim function. Could you please give me an example what should I do in my case?

    I had the same problem, sorting the same type of data in Excel, but couple of guys in an Excel forum helped me with a Macro that sorts the data perfectly.
    I hoped to get such kind of a solution in Access as well.

    Best regrds
    Last edited by Kensei; 01-09-16 at 14:44.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    for details on LTrim see da manuel or do a google search, lots of descriptions of what the function and lots of examples

    as to how you handle the leading alphabetics well thats down to you and what those values are meant to represent.
    A standard alphanumeric sort order is ascending numbers, ascending letters ( some treat upper and lowercase differently, some do not) so for each character its sorted 0....9,A..Z and so on. a numeric sort order is in ascending value order

    so your padding rule needs to handle the numeric portion assumign that a leading alpha is alwasy sorted the smae way.
    but you will hit the same problem with S25.1.4, S108b, S108b.1, S108b.3 UNLESS you pad the numeric data

    S025.1.4, S108b, S108b.1, S108b.3
    OR
    S 25.1.4, S108b, S108b.1, S108b.3

    you can always strip out leading 0 using the LTRIM function
    you can always strip out leading 0 using the replace function if the zero's are embedded, again da manuel is your friend, or failing that use a search engine

    if you have already found a mechanism that works in Excel then port that into Access VBA the underlying language is the same HOWEVER the object model and some of the functions are different or not present in one or t'other.

    Bear in mind its tough for people to provide assistance when you don't provide actual sample data. ultimatlel its your app you need to do the spadework, if for no other reason than it makes you think about the problem and help you come to a solution. the fact that you have had a macro culled from elsewhere that works for Excel and you are unable or unwilling to mnake an effort to apply it to Access VBA speaks volumes for you development skills.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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