<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">
<TITLE>Message</TITLE>
<META content="MSHTML 6.00.2800.1170" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><SPAN class=022394710-08052003><FONT face=Arial color=#0000ff size=2>I
won't ask why so will this work for you? SchoolOrder is a text
field.</FONT></SPAN></DIV>
<DIV><SPAN class=022394710-08052003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=022394710-08052003>
<TABLE cellSpacing=0 bgColor=#ffffff border=1><FONT face="" color=#000000>
<CAPTION><B>Table1</B></CAPTION></FONT>
<THEAD>
<TR>
<TH borderColor=#000000 bgColor=#c0c0c0><FONT style="FONT-SIZE: 10pt"
face="" color=#000000>SchoolID</FONT></TH>
<TH borderColor=#000000 bgColor=#c0c0c0><FONT style="FONT-SIZE: 10pt"
face="" color=#000000>SchoolOrder</FONT></TH></TR></THEAD>
<TBODY>
<TR vAlign=top>
<TD borderColor=#c0c0c0 align=right><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>1</FONT></TD>
<TD borderColor=#c0c0c0><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>001.001</FONT></TD></TR>
<TR vAlign=top>
<TD borderColor=#c0c0c0 align=right><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>4</FONT></TD>
<TD borderColor=#c0c0c0><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>001.002</FONT></TD></TR>
<TR vAlign=top>
<TD borderColor=#c0c0c0 align=right><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>6</FONT></TD>
<TD borderColor=#c0c0c0><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>001.003</FONT></TD></TR>
<TR vAlign=top>
<TD borderColor=#c0c0c0 align=right><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>5</FONT></TD>
<TD borderColor=#c0c0c0><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>001.004</FONT></TD></TR>
<TR vAlign=top>
<TD borderColor=#c0c0c0 align=right><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>2</FONT></TD>
<TD borderColor=#c0c0c0><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>002.001</FONT></TD></TR>
<TR vAlign=top>
<TD borderColor=#c0c0c0 align=right><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>8</FONT></TD>
<TD borderColor=#c0c0c0><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>002.002</FONT></TD></TR>
<TR vAlign=top>
<TD borderColor=#c0c0c0 align=right><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>10</FONT></TD>
<TD borderColor=#c0c0c0><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>002.003</FONT></TD></TR>
<TR vAlign=top>
<TD borderColor=#c0c0c0 align=right><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>12</FONT></TD>
<TD borderColor=#c0c0c0><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>002.004</FONT></TD></TR>
<TR vAlign=top>
<TD borderColor=#c0c0c0 align=right><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>14</FONT></TD>
<TD borderColor=#c0c0c0><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>002.005</FONT></TD></TR>
<TR vAlign=top>
<TD borderColor=#c0c0c0 align=right><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>15</FONT></TD>
<TD borderColor=#c0c0c0><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>002.006</FONT></TD></TR>
<TR vAlign=top>
<TD borderColor=#c0c0c0 align=right><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>18</FONT></TD>
<TD borderColor=#c0c0c0><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>002.007</FONT></TD></TR>
<TR vAlign=top>
<TD borderColor=#c0c0c0 align=right><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>20</FONT></TD>
<TD borderColor=#c0c0c0><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>002.008</FONT></TD></TR>
<TR vAlign=top>
<TD borderColor=#c0c0c0 align=right><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>21</FONT></TD>
<TD borderColor=#c0c0c0><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>002.009</FONT></TD></TR>
<TR vAlign=top>
<TD borderColor=#c0c0c0 align=right><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>23</FONT></TD>
<TD borderColor=#c0c0c0><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>002.010</FONT></TD></TR>
<TR vAlign=top>
<TD borderColor=#c0c0c0 align=right><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>25</FONT></TD>
<TD borderColor=#c0c0c0><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>002.011</FONT></TD></TR>
<TR vAlign=top>
<TD borderColor=#c0c0c0 align=right><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>3</FONT></TD>
<TD borderColor=#c0c0c0><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>003.001</FONT></TD></TR>
<TR vAlign=top>
<TD borderColor=#c0c0c0 align=right><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>9</FONT></TD>
<TD borderColor=#c0c0c0><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>003.002</FONT></TD></TR>
<TR vAlign=top>
<TD borderColor=#c0c0c0 align=right><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>11</FONT></TD>
<TD borderColor=#c0c0c0><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>003.003</FONT></TD></TR>
<TR vAlign=top>
<TD borderColor=#c0c0c0 align=right><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>13</FONT></TD>
<TD borderColor=#c0c0c0><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>003.004</FONT></TD></TR>
<TR vAlign=top>
<TD borderColor=#c0c0c0 align=right><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>16</FONT></TD>
<TD borderColor=#c0c0c0><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>003.005</FONT></TD></TR>
<TR vAlign=top>
<TD borderColor=#c0c0c0 align=right><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>17</FONT></TD>
<TD borderColor=#c0c0c0><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>003.006</FONT></TD></TR>
<TR vAlign=top>
<TD borderColor=#c0c0c0 align=right><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>19</FONT></TD>
<TD borderColor=#c0c0c0><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>003.007</FONT></TD></TR>
<TR vAlign=top>
<TD borderColor=#c0c0c0 align=right><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>22</FONT></TD>
<TD borderColor=#c0c0c0><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>003.008</FONT></TD></TR>
<TR vAlign=top>
<TD borderColor=#c0c0c0 align=right><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>24</FONT></TD>
<TD borderColor=#c0c0c0><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>003.009</FONT></TD></TR>
<TR vAlign=top>
<TD borderColor=#c0c0c0 align=right><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>26</FONT></TD>
<TD borderColor=#c0c0c0><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>003.010</FONT></TD></TR>
<TR vAlign=top>
<TD borderColor=#c0c0c0 align=right><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>31</FONT></TD>
<TD borderColor=#c0c0c0><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>010.001</FONT></TD></TR>
<TR vAlign=top>
<TD borderColor=#c0c0c0 align=right><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>32</FONT></TD>
<TD borderColor=#c0c0c0><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>010.002</FONT></TD></TR>
<TR vAlign=top>
<TD borderColor=#c0c0c0 align=right><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>33</FONT></TD>
<TD borderColor=#c0c0c0><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>011.001</FONT></TD></TR>
<TR vAlign=top>
<TD borderColor=#c0c0c0 align=right><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>35</FONT></TD>
<TD borderColor=#c0c0c0><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>011.002</FONT></TD></TR>
<TR vAlign=top>
<TD borderColor=#c0c0c0 align=right><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>36</FONT></TD>
<TD borderColor=#c0c0c0><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>011.003</FONT></TD></TR>
<TR vAlign=top>
<TD borderColor=#c0c0c0 align=right><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>34</FONT></TD>
<TD borderColor=#c0c0c0><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>012.001</FONT></TD></TR>
<TR vAlign=top>
<TD borderColor=#c0c0c0 align=right><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>37</FONT></TD>
<TD borderColor=#c0c0c0><FONT style="FONT-SIZE: 10pt" face=""
color=#000000>012.002</FONT></TD></TR></TBODY>
<TFOOT></TFOOT></TABLE></SPAN></DIV>
<DIV> </DIV>
<DIV> </DIV><!-- Converted from text/rtf format -->
<P><B><I><FONT face="Times New Roman" color=#0000ff size=4>John V.
Ruff</FONT><FONT face="Times New Roman" color=#008080 size=4> – The Eternal
Optimist</FONT><FONT face="Times New Roman" size=4></FONT> <FONT face=Wingdings
color=#800080 size=4>J</FONT></I></B><I></I> <BR><I><FONT
face="Times New Roman">Always Looking For Contract Opportunities</FONT></I> </P>
<P><FONT face="Times New Roman" size=2>Home: 253.588.2139</FONT> <BR><FONT
face="Times New Roman" size=2>Cell: 253.307/2947</FONT> <BR><FONT
face="Times New Roman" size=2>9306 Farwest Dr SW</FONT> <BR><FONT
face="Times New Roman" size=2>Lakewood, WA 98498</FONT> </P>
<P><B><I><FONT face="Times New Roman" color=#000080 size=2>“Commit to the Lord
whatever you do,</FONT></I></B> <BR><B><I><FONT face="Times New Roman"
color=#000080
size=2>
and your plans will succeed.” Proverbs 16:3</FONT></I></B><I></I> </P>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left><FONT
face=Tahoma size=2>-----Original Message-----<BR><B>From:</B>
accessd-bounces@databaseadvisors.com
[mailto:accessd-bounces@databaseadvisors.com] <B>On Behalf Of </B>Ervin
Brindza<BR><B>Sent:</B> Thursday, May 08, 2003 3:38 AM<BR><B>To:</B>
AccessD@databaseadvisors.com<BR><B>Subject:</B> [AccessD] Insert record into
fake group<BR><BR></FONT></DIV>
<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></BLOCKQUOTE></BODY></HTML>