how can I get secuencial sorting

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

the question that I have is . I have to sort a query in a secuential matter for exaple of what I get now

JobNumber
04-0609
04-0610
04-06100
04-06101 etc.....

when I need to get

JobNumber
04-0609
04-0610
04-0611
04-0612 etc.....

I read another person post that had a similar problem but I did not get how to make it work can someone siplified how to fix this.

Thanks in advance
Rafael
 
If all of your numbers are of that format (digits, dash, more digits), try
adding 2 computed fields to your query (you don't need to display them if
you don't want to) and sort on them.

FirstPart: CLng(Left([JobNumber], InStr([JobNumber], "-") - 1))
SecondPart: CLng(Mid([JobNumber], InStr([JobNumber], "-") + 1))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Rafael said:
the question that I have is . I have to sort a query in a secuential
Click to expand...
matter for exaple of what I get now
JobNumber
04-0609
04-0610
04-06100
04-06101 etc.....

when I need to get

JobNumber
04-0609
04-0610
04-0611
04-0612 etc.....

I read another person post that had a similar problem but I did not get
Click to expand...
how to make it work can someone siplified how to fix this.
 
sorry I'm in a beginner lever access, I realy dont know where I would place that.

here is my sql if that is were I have to place it.

SELECT [Job-Book].[JobNumber], [Job-Book].[Client name], [Job-Book].[completedsurvey], [Job-Book].[completedsiteplan], [Job-Book].[completedtiein], [Job-Book].[completedstake], [Job-Book].[completedtieinfinal], [Job-Book].[invocedsurvey], [Job-Book].[invocedsiteplan], [Job-Book].[invocedtiein], [Job-Book].[invocedstake], [Job-Book].[invocedtieinfinal], [Job-Book].[ressurvey], [Job-Book].[ressiteplan], [Job-Book].[restiein], [Job-Book].[resstake], [Job-Book].[restieinfinal]
FROM [Job-Book]
WHERE ((([Job-Book].[Client name])=[customer]));

thanks again a million
Raf
ps. I been at this problem since 8 am so you can see that I'm realy a beginner.

Douglas J. Steele said:
If all of your numbers are of that format (digits, dash, more digits), try
adding 2 computed fields to your query (you don't need to display them if
you don't want to) and sort on them.

FirstPart: CLng(Left([JobNumber], InStr([JobNumber], "-") - 1))
SecondPart: CLng(Mid([JobNumber], InStr([JobNumber], "-") + 1))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Rafael said:
the question that I have is . I have to sort a query in a secuential
Click to expand...
matter for exaple of what I get now
JobNumber
04-0609
04-0610
04-06100
04-06101 etc.....

when I need to get

JobNumber
04-0609
04-0610
04-0611
04-0612 etc.....

I read another person post that had a similar problem but I did not get
Click to expand...
how to make it work can someone siplified how to fix this.
Thanks in advance
Rafael
Click to expand...
Click to expand...
 
Add Doug's recommendation to your select clause. Try this:

SELECT [Job-Book].[JobNumber], [Job-Book].[Client name],
[Job-Book].[completedsurvey], [Job-Book].[completedsiteplan],
[Job-Book].[completedtiein], [Job-Book].[completedstake],
[Job-Book].[completedtieinfinal], [Job-Book].[invocedsurvey],
[Job-Book].[invocedsiteplan], [Job-Book].[invocedtiein],
[Job-Book].[invocedstake], [Job-Book].[invocedtieinfinal],
[Job-Book].[ressurvey], [Job-Book].[ressiteplan], [Job-Book].[restiein],
[Job-Book].[resstake], [Job-Book].[restieinfinal],
FirstPart CLng(Left([JobNumber], InStr([JobNumber], "-") - 1)),
SecondPart CLng(Mid([JobNumber], InStr([JobNumber], "-") + 1))
FROM [Job-Book]
WHERE [Job-Book].[Client name])=[customer]
ORDER BY FirstPart, SecondPart;

OR


SELECT [Job-Book].[JobNumber], [Job-Book].[Client name],
[Job-Book].[completedsurvey], [Job-Book].[completedsiteplan],
[Job-Book].[completedtiein], [Job-Book].[completedstake],
[Job-Book].[completedtieinfinal], [Job-Book].[invocedsurvey],
[Job-Book].[invocedsiteplan], [Job-Book].[invocedtiein],
[Job-Book].[invocedstake], [Job-Book].[invocedtieinfinal],
[Job-Book].[ressurvey], [Job-Book].[ressiteplan], [Job-Book].[restiein],
[Job-Book].[resstake], [Job-Book].[restieinfinal]
FROM [Job-Book]
WHERE [Job-Book].[Client name]=[customer]
ORDER BY CLng(Left([JobNumber], InStr([JobNumber], "-") - 1)),
CLng(Mid([JobNumber], InStr([JobNumber], "-") + 1))


sorry I'm in a beginner lever access, I realy dont know where I would place that.

here is my sql if that is were I have to place it.

SELECT [Job-Book].[JobNumber], [Job-Book].[Client name], [Job-Book].[completedsurvey], [Job-Book].[completedsiteplan], [Job-Book].[completedtiein], [Job-Book].[completedstake], [Job-Book].[completedtieinfinal], [Job-Book].[invocedsurvey], [Job-Book].[invocedsiteplan], [Job-Book].[invocedtiein], [Job-Book].[invocedstake], [Job-Book].[invocedtieinfinal], [Job-Book].[ressurvey], [Job-Book].[ressiteplan], [Job-Book].[restiein], [Job-Book].[resstake], [Job-Book].[restieinfinal]
FROM [Job-Book]
WHERE ((([Job-Book].[Client name])=[customer]));

thanks again a million
Raf
ps. I been at this problem since 8 am so you can see that I'm realy a beginner.

:

If all of your numbers are of that format (digits, dash, more digits), try
adding 2 computed fields to your query (you don't need to display them if
you don't want to) and sort on them.

FirstPart: CLng(Left([JobNumber], InStr([JobNumber], "-") - 1))
SecondPart: CLng(Mid([JobNumber], InStr([JobNumber], "-") + 1))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


the question that I have is . I have to sort a query in a secuential
Click to expand...

matter for exaple of what I get now
JobNumber
04-0609
04-0610
04-06100
04-06101 etc.....

when I need to get

JobNumber
04-0609
04-0610
04-0611
04-0612 etc.....

I read another person post that had a similar problem but I did not get
Click to expand...

how to make it work can someone siplified how to fix this.
Thanks in advance
Rafael
Click to expand...
Click to expand...
Click to expand...
 
Actually, if including the fields in the SELECT part of the SQL, you need to
rewrite it slightly to use the AS keyword. Also, you cannot use the
correlation name in the ORDER BY: you need to repeat the functions.

SELECT [Job-Book].[JobNumber], [Job-Book].[Client name],
[Job-Book].[completedsurvey], [Job-Book].[completedsiteplan],
[Job-Book].[completedtiein], [Job-Book].[completedstake],
[Job-Book].[completedtieinfinal], [Job-Book].[invocedsurvey],
[Job-Book].[invocedsiteplan], [Job-Book].[invocedtiein],
[Job-Book].[invocedstake], [Job-Book].[invocedtieinfinal],
[Job-Book].[ressurvey], [Job-Book].[ressiteplan], [Job-Book].[restiein],
[Job-Book].[resstake], [Job-Book].[restieinfinal],
CLng(Left([JobNumber], InStr([JobNumber], "-") - 1)) AS FirstPart ,
CLng(Mid([JobNumber], InStr([JobNumber], "-") + 1)) AS SecondPart
FROM [Job-Book]
WHERE [Job-Book].[Client name])=[customer]
ORDER BY CLng(Left([JobNumber], InStr([JobNumber], "-") - 1)),
CLng(Mid([JobNumber], InStr([JobNumber], "-") + 1))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Shaun Beane said:
Add Doug's recommendation to your select clause. Try this:

SELECT [Job-Book].[JobNumber], [Job-Book].[Client name],
[Job-Book].[completedsurvey], [Job-Book].[completedsiteplan],
[Job-Book].[completedtiein], [Job-Book].[completedstake],
[Job-Book].[completedtieinfinal], [Job-Book].[invocedsurvey],
[Job-Book].[invocedsiteplan], [Job-Book].[invocedtiein],
[Job-Book].[invocedstake], [Job-Book].[invocedtieinfinal],
[Job-Book].[ressurvey], [Job-Book].[ressiteplan], [Job-Book].[restiein],
[Job-Book].[resstake], [Job-Book].[restieinfinal],
FirstPart CLng(Left([JobNumber], InStr([JobNumber], "-") - 1)),
SecondPart CLng(Mid([JobNumber], InStr([JobNumber], "-") + 1))
FROM [Job-Book]
WHERE [Job-Book].[Client name])=[customer]
ORDER BY FirstPart, SecondPart;

OR


SELECT [Job-Book].[JobNumber], [Job-Book].[Client name],
[Job-Book].[completedsurvey], [Job-Book].[completedsiteplan],
[Job-Book].[completedtiein], [Job-Book].[completedstake],
[Job-Book].[completedtieinfinal], [Job-Book].[invocedsurvey],
[Job-Book].[invocedsiteplan], [Job-Book].[invocedtiein],
[Job-Book].[invocedstake], [Job-Book].[invocedtieinfinal],
[Job-Book].[ressurvey], [Job-Book].[ressiteplan], [Job-Book].[restiein],
[Job-Book].[resstake], [Job-Book].[restieinfinal]
FROM [Job-Book]
WHERE [Job-Book].[Client name]=[customer]
ORDER BY CLng(Left([JobNumber], InStr([JobNumber], "-") - 1)),
CLng(Mid([JobNumber], InStr([JobNumber], "-") + 1))


sorry I'm in a beginner lever access, I realy dont know where I would place that.

here is my sql if that is were I have to place it.

SELECT [Job-Book].[JobNumber], [Job-Book].[Client name],
Click to expand...
Click to expand...
[Job-Book].[completedsurvey], [Job-Book].[completedsiteplan],
[Job-Book].[completedtiein], [Job-Book].[completedstake],
[Job-Book].[completedtieinfinal], [Job-Book].[invocedsurvey],
[Job-Book].[invocedsiteplan], [Job-Book].[invocedtiein],
[Job-Book].[invocedstake], [Job-Book].[invocedtieinfinal],
[Job-Book].[ressurvey], [Job-Book].[ressiteplan], [Job-Book].[restiein],
[Job-Book].[resstake], [Job-Book].[restieinfinal]
FROM [Job-Book]
WHERE ((([Job-Book].[Client name])=[customer]));

thanks again a million
Raf
ps. I been at this problem since 8 am so you can see that I'm realy a beginner.

:

If all of your numbers are of that format (digits, dash, more digits), try
adding 2 computed fields to your query (you don't need to display them if
you don't want to) and sort on them.

FirstPart: CLng(Left([JobNumber], InStr([JobNumber], "-") - 1))
SecondPart: CLng(Mid([JobNumber], InStr([JobNumber], "-") + 1))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



the question that I have is . I have to sort a query in a secuential

matter for exaple of what I get now

JobNumber
04-0609
04-0610
04-06100
04-06101 etc.....

when I need to get

JobNumber
04-0609
04-0610
04-0611
04-0612 etc.....

I read another person post that had a similar problem but I did not get

how to make it work can someone siplified how to fix this.

Thanks in advance
Rafael
Click to expand...
Click to expand...
Click to expand...
 
Back
Top