My access front-end uses VAL function to sort the out put so that the non-numeric values are ignored in the sort. My list displayed with the above data set will look as follows...

10
10D
20
21B
32
38B
100
112
213
381

We have recently migrated our backend to SQL Server. Since then this query stopped working since VAL is not a recognized function in SQL. Since I can not use SQL specific function in Access query, I have written the following stored procedure to get the work done.

CREATE PROCEDURE sp_GetTestList
AS
BEGIN
-- Sorts the Test list appropriately.
-- Without this sort the list would display 10, 100, 10D, 112, 20, 213, 21B, 32, 381, 38B
-- This proc would return the above Test list as 10, 10D, 20, 21B, 32, 38B, 100, 112, 213, 381

SELECT tblTest.Test
FROM tblTest (NOLOCK)
ORDER BY
CAST (


CASE SIGN (PATINDEX ('%[0-9]%', tblTest.Test))
WHEN 0 THEN NULL
ELSE
CASE SIGN (PATINDEX ('%[^0-9]%', tblTest.Test))
WHEN 0 THEN tblTest.Test
ELSE LEFT (tblTest.Test, PATINDEX ('%[^0-9]%', tblTest.Test) -1)
END
END
AS Numeric),
CASE SIGN (PATINDEX ('%[^0-9]%', tblTest.Test))
WHEN 0 THEN NULL
ELSE RIGHT (tblTest.Test, LEN (tblTest.Test) - (PATINDEX ('%[^0-9]%', tblTest.Test)) + 1)
END
END
GO

I have linked this proc to be used as a pass through query in access. It works as desired for sorting. But now I lost the ability to add new rows. What should I do so that I can add new records to the list displayed? Access didn't have a problem with it. I have tried changing it to a view with TOP 100 PERCENT option and that did not work either. This is the only column in this table. It is unique and is the primary key for this table.

Please help me in finding a resolution for this issue.

Regards,
Helios