Sorting Access Table

[Brlee1crv]

Greetings,
I have a table that have entries labeled alphabetically and numerically (ie: AP04001, AP04002, AP04003). The "AP" will always stay the same, "04" is for the current year, and the last three numbers correspond to the entry number. I am having a problem when it comes to anything over 999. For example if a job is labeled AP041000 it won't come after AP04999 in the table. It will be after AP04100. How can I fix this? Thanks

 

[PaulF]

in the table, not without renumbering the values using leading zeros.. but in a query you can create two additional fields that you can sort on .. a Sort Year and a Sort Seq and you can sort on these

if your fieldname is EntryNumber then try these

SortYear: Mid([EntryNumber],3,2)

SortSeq: Val(Nz(Mid([EntryNumber],5),0))

then you can use these fields as the sort fields in reports and forms

Example for a Form

Private Sub Form_Load()
Me.OrderBy = "[SortYear],[SortSeq]"
Me.OrderByOn = True
End Sub

For a Report, if you use the query you should be able to set it as you build if (if you use the built-in wizard) or via the Sorting and Grouping Dialog box accessed from the View menu.

PaulF

 

[JeremyNYC]

Paul's reply is good, but let me push you a little bit...

You should not be storing that data in one field. It's two bits of information, and they should be stored in two fields and then concatenated on the fly as needed.

Also, you should let go of the notion of sorting tables. Tables are not stored with any particular sort order. You can sort them, and Access will ask if you want to save the layout changes, and the next time you open it, things will be in the same order as you sorted. Sometimes. But if records have changed or been added, your sort will be hosed. In general, keep your and your users' hands off the tables. Adding, deleting, modifying should all be done through forms.

Jeremy

---
Jeremy Wallace
METRIX Project Coordinator
Fund for the City of New York

 

[Brlee1crv]

Thank you both for your replies. Unfortunately I inherited the database with this table so I'm trying to find a way to get out of this bind. I'm about 4000 records deep and all the records are in this format. The reason I would like the tables sorted is because when I run a query the results aren't in the correct numerical order and it throws things off.

 

[PHV]

when I run a query the results aren't in the correct numerical order
Replace the ORDER BY [field name] clause by something like this:
ORDER BY Left([field name],4), Val(Mid([field name],5))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[BNPMike]

As others have said, you should never need to sort a table. What ever you are trying to achieve can be better done via a query which will re-format (including sorting) your data into the form you want it for the task at hand.

Tables mirror the underlying abstract data model. Programs access this via views - which are queries in Jet.