Saturday, July 23, 2005

Microsoft Access SQL Query Bug

I'm getting crazy, found that the TOP # in the Microsoft Access Database is having a bug!

While I'm trying to do this for one access database, it gives me 18 records!!

SELECT TOP 10 Right([FromYear],4) AS FromYear, Right([ToYear],4) AS ToYear, 'spm' + mid([Reference No],1,4) + '-' + mid([Reference No],5,2) + '-' + mid([Reference No],7,2) + '-' + mid([Reference No],9,3) + '.jpg' AS PicRefNo, [Reference No] AS AccessionNo, [Item] FROM tblStamps WHERE 1=1 AND [Description] LIKE '%king%' AND [Reference No] NOT IN (SELECT TOP 10 [Reference No] FROM tblStamps WHERE 1=1 AND [Description] LIKE '%king%' ORDER BY FromYear, ToYear ) ORDER BY FromYear, ToYear

I believe it is the ORDER BY which confused up the TOP, and both of them can't be used together (not compatible).


No comments: