Results 1 to 9 of 9
  1. #1
    gmaster is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    May 2014
    Posts
    32

    Sort records by date added

    Is there a way to sort records in a table by their add date?


    I have a field that is filled like this: A[number ascending] (A1, A2,...A224 etc...), and i want Access to keep the number order, not the alphabetical....'cause it aumatically sort it by alphabetical, and it's not the order i want...:/

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    You would have to parse the text. You could use the Left() function if the alpha part is always a certain number of characters (your example indicates one character). Otherwise, you would have to write out some code to analyze the literal text as a string variable. You would need to parse each character and determine the position of the end of alpha and the beginning of number. With this information you could use the Left() function to parse only the characters of type Number.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Further to ItsMe's response, how did these "codes" get assigned. What is the order you really need? You would use a Select query with an Order By, but you may have to calculate the value you need. This sounds a little like a design issue where the functionality you need has not been designed into the structure. But there may be other issues that we are not aware of or that you haven't described...
    If you have a field eg DateAdded, you could Order By DateAdded

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    While I agree with itsme and orange, I have come across databases that were not my own that needed to do something similar so you can parse the number value out with a formula something like (assuming your codes are indeed one alpha character at the beginning)

    right([CodeValue], len(codevalue) - 1)

    if, as itsme suggests the code is more complex and can be a string of any length this would become more complex by far, unless the alpha part of the code can be found somewhere else in your dataset, then you could just trim off the alpha characters.

  5. #5
    Subwind is offline Can Only Learn
    Windows XP Access 2010 32bit
    Join Date
    May 2012
    Location
    Portsmouth, UK
    Posts
    61
    Sort by the date field? If you have no date field, then I don't think there is anyway you can sort by the date. The guys above are talking about sorting that ID field in order, but why don't you sort by a date field instead?

    Could you post a copy of the table you are trying to sort?

    ~Matt

  6. #6
    Xipooo's Avatar
    Xipooo is offline Sr. Database Developer
    Windows 8 Access 2013
    Join Date
    Jan 2014
    Location
    Arizona
    Posts
    332
    I was going to say the same thing Matt said. How do you order by the date if there's no date field? Either add one or you're stuck with using no Order By clause at all in your query and hoping Access displays them in order. Heck, even an autonumber field would help.

  7. #7
    gmaster is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    May 2014
    Posts
    32
    You're right guys, but i can't add a date field i can only work with those alphanumeric fields.....
    So is there a way to sort them accounting only the numbers? (the letter is always the same, always A)

  8. #8
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I already told you how

  9. #9
    gmaster is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    May 2014
    Posts
    32
    It works, thanks!!!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 02-16-2013, 11:36 AM
  2. Sort Records Via Date (Newest first)
    By tweety in forum Forms
    Replies: 4
    Last Post: 01-28-2013, 09:23 AM
  3. Adding the Date & Time record added
    By jo15765 in forum Access
    Replies: 2
    Last Post: 11-26-2010, 11:31 PM
  4. Replies: 2
    Last Post: 02-17-2010, 09:53 PM
  5. Replies: 4
    Last Post: 01-29-2009, 02:43 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums