bestiosus
asked on
SQL/Access insert zero into field/number string
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!
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!
but that's probably not how you wan't it sorted....
Maybe this query would be better....
SELECT Table2.Field1
FROM Table2
ORDER BY CDbl(Mid([Field1],5)), Table2.Field1;
SELECT Table2.Field1
FROM Table2
ORDER BY CDbl(Mid([Field1],5)), Table2.Field1;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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