Sorting by number problem

[Guest]

I have a table that contains a "job #" that corresponds to a "project name."
Each project has a different job number. The job number is four digits. The
first two digits are the last two digits of the year the project was started,
and the last two digits are in the order that the project was received.

The problem is that the jobs from 2000-present, (job numbers begin with
"00", "01", and so on) get sorted before jobs from 1999 and before (job
numbers begin with "99", "98", and so on. This is assuming I will sort
records in ascending order. I know this is probably a common question, and I
can't really think of an easy solution. I can't change the format of the job
number because this is how all our paper and electronic files are organized.
Any suggestions?

 

[Rick B]

Generally, you should not store two types of data in one field. The job
number should be in one field, the year created in another.

If you want to display these on reports as last two digits of the year + two
digit job number, you can do so in an unbound text box.

=Right([JobYear],2) & [JobNo]



Storing these as two separate fields would allow you to sort by year, then
job number. It would also better match Access normalization rules.

 

[George Nicholson]

Add the 4 digit Project year to your queries/reports. Maybe you can pull
this from the Project table, if the field already exists. If not you can add
a new field to your queries:
ProjectYear = Year(DateSerial(Left([ProjNum],2),1,1))
If ProjNum = 0156 then ProjectYear = 2001
Then sort ProjectYear & JobNumber together. That would put 1998 before 2005
projects while leaving them in sequence within any given year.

HTH,