<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=windows-1252">
<META content="MSHTML 5.50.4134.600" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><FONT face=Arial size=2>
<DIV><FONT face=Arial size=2>Cross posted</FONT></DIV>
<DIV><FONT face=Arial size=2>Hi,<BR>I need a "little" advice about record order.
The fields are: "SchoolId"(Integer) and "SchoolOrder"(Text) which cause the
problem. The field "SchoolOrder" is text, not number, because there is a need to
insert a new school between two existing ones.<BR>For example:<BR>SchoolID
SchoolOrder<BR>1
1<BR>2
2<BR>3
3<BR>and the customer want to insert the fourth school between the first and the
second(the new school is somehow related to the first school, creating a "fake
group" with the first) then the SchoolOrder is 10 and:<BR>SchoolID
SchoolOrder<BR>1
1<BR>4
10<BR>2
2<BR>3
3<BR>The pair(5, 11) will be on the 3. place, the (6, 101) will be behind the
(4,10) etc.
<BR>1
1<BR>4
10<BR>6
101<BR>5
11<BR>2
2<BR>3
3</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>This is all right but what should I do when there
are more than 9 "fake groups".<BR>I tried with:<BR>SchoolID
SchoolOrder<BR>1
1/1<BR>4
1/2<BR>6
1/3<BR>5
1/4<BR>2
2/1<BR>3
3/1<BR>and in the SQL in the ORDER BY clause to put(separate the left and the
right side from the
"/"):<BR>Mid([tblSchool].[SchoolOrder],1,InStr([tblSchool].[SchoolOrder],"/")-1)
&
Mid([tblSchool].[SchoolOrder],InStr([tblSchool].[SchoolOrder],"/")+1,Len([tblSchool].[SchoolOrder]));</FONT></DIV>
<DIV><FONT face=Arial size=2>I tried wrapping into CInt function but no success
too.<BR>I know that additional field to separate the field SchoolOrder parts
would solve my problem, but the customer diesn't like the idea to modify the
table structure.<BR>Can anyone shed any light?</FONT></DIV>
<DIV><FONT face=Arial size=2>Many thanks in advance,</FONT></DIV>
<DIV><FONT face=Arial size=2>
Ervin</FONT></DIV></FONT></DIV></BODY></HTML>