NUMERIC Search on Text Field (1 Viewer)

[GUIDO22]

Hope can help - a simple way to illustrate my problem...


1.Create a simple database with a table and a 'short text' field, call it ReqNo
2. Add the following records to the table for ReqNo : 2a334,999,1231,34595
3. Run the following query against this table.. :
SELECT Max(ReqNo) AS Expr1
FROM Table1
WHERE (((IsNumeric([ReqNo]))<>False));

.... why is the returned value 999...? I had reasonably expected this to return 34595....

Background: I changed the datatype of a field in a crucial table within my production database to short text and now I am unable to retrieve the highest ReqNo from this table which is now causing me some problems...

Thanks in advance for any help you can give...

 

[CJ_London]

Because in text, 999 is greater than 34595

it sorts

1
11
2
21
Etc

try max(clng(reqno))

 

[Gasman]

That is how alpha is calculated.

 

[GUIDO22]

Because in text, 999 is greater than 34595

it sorts

1
11
2
21
Etc

try max(clng(reqno))

Thank you very much - that works a treat ... as soon as I read your answer I have seen this result before on other development projects I have worked on and swerved finding a solution.. !

 

[isladogs]

Try this (untested)

SELECT Max(CLng(RegNo)) As MaxRegNo
FROM Table1
WHERE IsNumeric(RegNo)=True