Data type mismatch in criteria expression

[Alephu5]

I am working on a fairly ancient manufacturing database that identifies items using a combination of letters and numbers. The usual format is to have a letter (which suggests something about the item type) followed by a sequence of numbers.

I am trying to write a query that looks up all the records beginning with a prefix or arbitrary length, strips away the text, and finds the highest number.

Thinking this would be a straightforward task I made the following query:

SELECT Right(LocalID,Len(LocalID) - 1)  As IDSuffix 
FROM tblItemIDCrossReference 
WHERE Left(LocalID,1) = 'T' AND IsNumeric(Right(LocalID,Len(LocalID) - 1)=True)

This query produces the error given in the title of this thread, whilst the following works:

SELECT Right(LocalID,Len(LocalID) - 1)  As IDSuffix 
FROM tblItemIDCrossReference 
WHERE Left(LocalID,1) = 'T' AND IsNumeric(Right(LocalID,5)=True)

This related query also works and shows a load of -1s and 0s correctly

SELECT Right(LocalID,Len(LocalID) - 1)  As IDSuffix,
IsNumeric(Right(LocalID,Len(LocalID) - 1)=True) As Alias
FROM tblItemIDCrossReference 
WHERE Left(LocalID,1) = 'T' AND

But once again shows the error message when I try to filter the field Alias to -1 or 0 only through the right-click menu.

I have tried piping Len(LocalID)-1 through CLng, CInt, Int, CDbl and CSng; this changes the error to 'Invalid Use Of Null' I have also tried removing the '=True' from the IsNumeric() term.

 

[billmeye]

Does LocalID always contain data? If it is null that could create the error.

 

[Alephu5]

Adding

WHERE Not IsNull(LocalID)

solved the problem. Thank you.