strange sort for access forms - I'm boggled.

[mseifman]

Let me start out by saying... this is a weird one from a company a little out of date.

Employees are assigned employee ID number based upon the year they were hired, and the next available number. For example: hired in 1999, 3rd employee hired - your employee number is 993. hired in 2005, 3rd employee hired - your employee number is 53.

Hire date is not kept anywhere.

I cannot change the data type, nor the content of the data. I must work with what is there.

I must sort employees in order by employee number, so that seniority can be seen. Those people hired in 1999 must appear before those hired in 2005.

Any ideas??

 

[PHV]

What happens for 10th employee hired in a year (before and after y2k) ?

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

 

[mseifman]

well.. it's a small company. They've never gone hired more than 9 a year. * shrugs* I still favor converting the employee ID numbers to a full year, but they don't want to do it.

 

[PHV]

You may try this:
ORDER BY Abs(Left(employeeID,Len(employeeID)-1)<29), employeeID

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

 

[Golom]

You could do something like

Select EmpID, ... other Fields...

From tbl

Order By IIF(Len(EmpID)>2,
         "19" & Left(EmpId,2) ,
         "200" & Left(EmpID,1)) & Right (EmpId, 1)

Subject to the conditions that
- They never hire more than 9 employees in a year and
- they fix this before 2010

 

[melaniecarr23]

What if you were to use a query and a calculated field, set as numerical with 2 decimal places.

In the calculated field idSort:
If id <100, (id+2000)/10
(if the id was 53, it would then become 2005.3
If the id <1000, (id+1900)/10 (993 would then become 1999.3)

Provided they never hire more than 9 people in a year, this would work for sorting according to date hired. If they go over, I don't know what you'd do.

 

[koresnordic]

Melanie - would that not be id+20000 and id+19000. Else 53 becomes 2053 which divided by 10 becomes 205.3 and 993 becomes 2893 divided by 10 is 289.3.



Graham