Sorting by text then by numeric or len

[DebbieDavis]

Greetings! I have a strange request. I have a table with job numbers and a form where users list the jobs using the following query:

SELECT PLAN.* FROM PLAN ORDER BY PLAN.JOBID DESC;

The data type is text because the templates they use are text names SITR but the job numbers are numerical. Well it was working great until the job numbers turned 10000. Now the form lists all of the templates first (like they want)(SITR,PULT) but instead of being SITR, PULT, 10000, 9999, it's SITR, PULT, 9999, 9998 and 10000 is at the bottom. Is there anyway I can phenagle my query with some sort of len function and if statement? List the templates, then if len(JOBID)=5 then blah else blah? I'm not very strong with Access forms and queries. Any thoughts would be appreciated. Thanks.

 

[DebbieDavis]

Nevermind! I decided to fool the system by using a temporary ID field and sorting on that. Works great.

 

[TheAceMan1]

How are ya DebbieDavis . . . . .

Add the following custom field to the query:

[purple]numSort: Val(JOBID)[/purple]

Then your [blue]Order By[/blue] becomes:
[blue]ORDER BY PLAN.JOBID DESC, numSort DESC;[/blue]

See Ya! . . . . . .