Access Not Retrieving Correct Records Due to Criteria Issue

[John_G]

Your issue is that you cannot use aliases (e.g. [serial number left]) in the order by - you have to use the original expression, or the original field names.

You might find it a lot easier if you wrote a function to generate the numeric version (it could be similar to the one I posted above). You would avoid all the nested IIf's and it could work with any length serial number string. It would also make the query more manageable.



Note: Cint will fail with any number over 32767 - use CLng instead.

[davegri]

As John_G suggested, write a function to extract the numeric portions, then incorporate that function in the query. It really simplifies the query.
Put the query in a module so that it call be called from anywhere. I don't know what all the [serial number left] and [serial number right] selections were about, I just started at the left end and plowed thru to the end at the right.

'Serial number extract
Public Function snExt(S_N As String) As Long
    Dim i As Long
    For i = 1 To Len(S_N)
        If IsNumeric(Mid(S_N, i, 1)) Then
            snExt = snExt & Mid(S_N, i, 1)
        End If
    Next
    snExt = CLng(snExt)
End Function

SELECT MASTER.[MFR MDL CODE], snExt(Master.[Serial Number]) as [Serial Number Whole], MASTER.[SERIAL NUMBER] , TCDS_Data.CODE
FROM MASTER LEFT JOIN TCDS_Data ON MASTER.[MFR MDL CODE] = TCDS_Data.CODE
WHERE (((MASTER.[MFR MDL CODE]) = '1151410'))
ORDER BY snExt(Master.[Serial Number]);

[astronomicalguy]

Well, it's done. I coded up the Serial function (read: I copied and pasted the Serial function). I had to change the result to Double from Long because I was getting an overflow error (I'm not sure that I understand why since my longest serial number is twelve digits and Long is supposed to handle up to eighteen or nineteen). I also had to incorporate the Nz function to handle the empty serial number fields. With those mods to the function and the function calls, the query is finally giving the correct results!!

Thanks davegri, John_G, and orange ... you guys are the best.