Results 1 to 11 of 11

Thread: Access sorting

  1. #1
    Join Date
    May 2011
    Posts
    7

    Unanswered: Access sorting

    I was called to fix an access table which wouldn't allow the last required digits to be entered into a text field, which is the primary key. The field had an input mask of \KLC\-00\-00;0. Data entered is in the formats KLC-00-023 or KL--00-023, the first two digits representing the year, and the last represent a contract number. Either format may have a -X (that is, some digit) following the contract number. The problem at that point was Access wouldn't allow entry of the -X. I don't know how it ever worked that way but it seems to have, and I don't think anypone in that department would have been tinkering with the design. Anyway, I changed the input mask to \KLC\-00\-000#9. Access now allows entry of required characters and digits, but the column doesn't sort properly. Data entered before the mask change sorts before date entered after the mask change. I pasted this column into Excel and it sorts the same way. Before and after data pasted into Excel cells side by side looks exactly the same, both are formated as "General". Anyone know how I can get this to sort correctly?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what sort order have you specified
    where have you specified it
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2011
    Posts
    7

    Access sort

    In the design view, Indexes, sorted Ascending.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    and what columns re specified
    what sequence are you seeing as opposed to expecting
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    May 2011
    Posts
    7

    Access Sort

    The primary key, Contract number, is specified. On opening the table, KL--00 thru KL--99 and KLC-00 through KLC-99 numbers that were entered before the input mask appear first. Then KL--00 numbers entered after the mask change appear at the bottom. Sorting Ascending on that field in the table view changes nothing. If I re-type one of the old numbers at the top of the column, then close and reopen, that record goes to its new place in the sort at the bottom of the column.

  6. #6
    Join Date
    May 2011
    Posts
    7
    In fact, re-typing any character in the contract number causes it to go to its new sort position near the bottom of the table.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    which makes me suspect that you have changed the way the data is stored (theres going to be extraneaous characters) added to the data

    one thing you could do is coerce the existing data to the new format.

    to do this you may need to create a new form, then iterate through each record in the table
    assign the contract number to a control that uses the same input mask
    modify the current contract number (add something, then remove that something then save the record.

    can you post a sample of data and the form and I'll try to work out what has happened
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    May 2011
    Posts
    7
    I was aftaid re-entering the data was the solution, but I hadn't thought of trying to automate the process.

    The data has been entered straight to the table, there is no form.
    There is only one table in the database and no other objects. I have deleted all but sample data from a copy of the database I can send, but how do I post it?

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    compact and repair the db to get rid of any wasted space
    zip it into a compressed library
    attach it to this thread

    ...as you have retrospectivley applied a format mask, what you could try to do is copy all the data to another table
    copy the structure of the current table
    then open it
    open the old table to view the data
    select all the records and paste them to the new table

    just thinking, you are applying a sort to an existing table, addign new records then the new records are at the foot of the table.. thats standard action for a table insert, new records get displayed at the bottom of the table. what happens if you open the table subsequently.

    bear in mind that a table is just a table you don't have any explicit ability to control the sequence, it will probably be in primary key order but you cannot guarantee it. do your self a favour and always use forms to capture data (you can do a heck of a lot more to validate your data in a form, than you can in a datasheet view of the table.
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    May 2011
    Posts
    7

    Access Sort

    I'm having some luck pasting to a new table, other than when their data doesn't fit their own criteria. I'm taking out the oddball entries and will let them deal with those after the fact. I'll get back to you.

  11. #11
    Join Date
    May 2011
    Posts
    7
    OK, I'm able to get the contract numbers to sort correctly by pasting the data into a duplicate table. Thanks for your help.

Tags for this Thread

Posting Permissions

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