[AccessD] Insert record into fake group

Hi Ervin

I guess you didn't invent this "system" ...?

Why not simply update the SchoolOrder:

  1            1
  4            2
  2            3
  3            4

After all, this must be the purpose of that column?

/gustav


> 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.
> For example:
> SchoolID SchoolOrder
> 1            1
> 2            2
> 3            3
> 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:
> SchoolID SchoolOrder
> 1            1
> 4            10
> 2            2
> 3            3

> The pair(5, 11) will be on the 3. place, the (6, 101) will be behind the (4,10) etc.
> 1            1
> 4            10
> 6            101
> 5            11
> 2            2
> 3            3

> This is all right but what should I do when there are more than 9 "fake groups".
> I tried with:
> SchoolID SchoolOrder
> 1            1/1
> 4            1/2
> 6            1/3
> 5            1/4
> 2            2/1
> 3            3/1
> and in the SQL in the ORDER BY clause to put(separate the left and the right side from the "/"):
> Mid([tblSchool].[SchoolOrder],1,InStr([tblSchool].[SchoolOrder],"/")-1) & Mid([tblSchool].[SchoolOrder],InStr([tblSchool].[SchoolOrder],"/")+1,Len([tblSchool].[SchoolOrder]));
> I tried wrapping into CInt function but no success too.
> 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.
> Can anyone shed any light?