Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Yreka, California, USA
    Posts
    187
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Sorting numbers in Access queries

    This is simply a question as to whether there is a better way to sort a field than the way we've worked with it in an expression. Here's the scenerio, we bring in a numeric field from an external database source it has six characters, but it can have a leading zero. The leading zero is dropped so in a sort the order is not correct. We've gotten around this by adding an expression that includes &Format([SeqNum], "000000") but we aren't sure that is the easiest way.

    Is there a way that we could change the number every time the number is imported so that any sort that is done thereafter is correct. This is one of our users' database, and he's now able to work with the data as he's added the expression, but in my own mind I'm wondering why can't the field be correctly formatted on import so that no matter what is done with the number it will sort correctly. My understanding is that the number is not used in calculations.

    Your comments and suggestions are most welcome,
    Thanks much, Judy Crawford

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,519
    Thanks
    3
    Thanked 50 Times in 50 Posts
    It appears you must be importing the data into an Access field that is a text field rather than numeric. Otherwise the data should sort correctly regardless of how you format it for display purposes. Or have I missed something obvious?
    Wendell

  3. #3
    New Lounger
    Join Date
    Jun 2010
    Location
    Gurnee, IL, USA
    Posts
    8
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Hi! It sounds like the source data is actually in a character format rather than number. You could do a conversion from character to number and store the result in the target field. That way, the data in the target will sort correctly. The conversion only happens at import time. The fact that the data in the source has a leading zero probably means it is formatted as text, probably in a fixed width field.

    Regards,
    Kirk

  4. #4
    Lounger
    Join Date
    Feb 2011
    Posts
    43
    Thanks
    0
    Thanked 6 Times in 6 Posts
    The source data field must be a text field and if the field in MS Access containing the imported data, drops any leading zero, it is a numeric field.

    Change the Access field to text.

    It is a common misunderstanding that an all digit field should be considered numeric. US and Australian zip/post codes are all digits but you never do numeric operations on them, similarly telephone numbers. These are text data and in all my systems are treated as such.

  5. #5
    New Lounger
    Join Date
    Dec 2009
    Location
    Milwaukee, WI USA
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You can also use the expression Val(SeqNum) in the query and sort on that. That gets the numeric value of the field. As mentioned in a number of other replies it definitely appears that the field is being treated as a character field and not a numeric value.

Posting Permissions

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