Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2005
    Location
    Mahopac, NY
    Posts
    1

    Question Unanswered: Modify a field's value for sorting in a report?

    Hi,
    I have a database which has a field called "Location". The values entered in this field are in one of two formats:
    #A#
    or,
    #A##,
    where A is a letter and # is a number.

    Now, for sorting purposes, this doesnt work, because if you have these two entries:
    1A32 and 1A4, it wants to sort by putting 1A32 before 1A4. However, for my purposes, 1A4 should go before 1A32. My question is, how can I modify these values for display in a report? I don't want to modify the original entries in the database, just change the format so that they will sort correctly in a report.

    I appreciate any help that anyone can provide!!

    Thanks!!

  2. #2
    Join Date
    Apr 2002
    Location
    Portugal
    Posts
    146
    Hi,

    the sort order for text data is:

    a,b,c,d, etc.. for letters
    1,10,100,1000,...,2,20,200,... for "numbers"

    if you have a text field, and you want to sort the numbers by the numerical sort order, forget it, unless you create another field that contains the order for the field you want to sort. Example:

    you have: 1A32;1A4 in field_1

    field_1 field_2
    create a field to put: 1A32 | 32
    1A4 | 4

    sort by field_2.

    Or set the numerical string part to a lengh, lets say, 3 and put the data as:
    1A032
    1A004
    1A022
    ....

Posting Permissions

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