<div>
Hello bestiosus,<br />
<br />
Without modifying the actual data, you could write a query to return you data sorted correctly. Something like this.....<br />
<br />
SELECT Table2.Field1<br />
FROM Table2<br />
ORDER BY Table2.Field1, Mid([Field1],5);<br />
<br />
This would return this....<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br />
NU-C1 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br />
NU-C2 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br />
NU-C21 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br />
NU-C22.1 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br />
NU-C3 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br />
<br />
<br />
Regards,<br />
<br />
Wayne
</div>


Hello bestiosus,

Without modifying the actual data, you could write a query to return you data sorted correctly. Something like this.....

SELECT Table2.Field1
FROM Table2
ORDER BY Table2.Field1, Mid([Field1],5);

This would return this....
      
NU-C1      
NU-C2      
NU-C21      
NU-C22.1      
NU-C3      


Regards,

Wayne

<div>
but that's probably not how you wan't it sorted....
</div>


but that's probably not how you wan't it sorted....

<div>
Maybe this query would be better....<br />
<br />
&nbsp; &nbsp; &nbsp;SELECT Table2.Field1<br />
&nbsp; &nbsp; &nbsp;FROM Table2<br />
&nbsp; &nbsp; &nbsp;ORDER BY CDbl(Mid([Field1],5)), Table2.Field1;
</div>


Maybe this query would be better....

     SELECT Table2.Field1
     FROM Table2
     ORDER BY CDbl(Mid([Field1],5)), Table2.Field1;

<div>
<div class="content wysiwyg-content">
I have a text string field in MS access. I am having problems ordering the field because of the numbers inserted in the string.<br />
<br />
Example strings:<br />
NU-C1<br />
NU-C2<br />
NU-C3<br />
...<br />
NU-C21<br />
NU-C22.1<br />
<br />
<br />
How can I write an sql/access code to adjust this to insert a zero in the string before a single digit. I was thinking something along the lines of a truncate to numbers then if length is =1... but I'm not sure how to pull it together. <br />
<br />
Ideas?<br />
Thanks!
</div>
</div>


I have a text string field in MS access. I am having problems ordering the field because of the numbers inserted in the string.

Example strings:
NU-C1
NU-C2
NU-C3
...
NU-C21
NU-C22.1


How can I write an sql/access code to adjust this to insert a zero in the string before a single digit. I was thinking something along the lines of a truncate to numbers then if length is =1... but I'm not sure how to pull it together. 

Ideas?
Thanks!

SQL/Access insert zero into field/number string

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

Microsoft Access

Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.