SELECT TOP 1 IS NOT WORK. WHY?

[regava]

I posted this request in a different forum and did not get a solution.
Access 2000 - I have the following query:

SELECT tblTracking.Tatno, tblTracking.Taxpayer, qryTick.Item, qryTick.ActDate
FROM tblTracking INNER JOIN qryTick ON tblTracking.Tatno = qryTick.TatNum
WHERE (((tblTracking.Consol)="N") AND ((tblTracking.Related)="N") AND ((IsNull([tblTracking].[ClosedDate]))<>False))
ORDER BY tblTracking.Tatno;

qryTick is a UNION query. When I executed the query as shown it returns lots of record which are correct. However, what I need is only one from each tblTracking.Tatno. I inserted TOP 1 after Select. I get only two records and only two records from the same tblTracking.Tatno. What am I doing wrong? Please help I need to solve the problem. I have done research during the last several days and have come out empty handed. THANK YOU.

 

[genomon]

Haven't looked at the TOP n problem, but for starters, get rid of all the parens. Then use SQL "AND [tblTracking].[ClosedDate] IS NULL" instead of the function "AND ((IsNull([tblTracking].[ClosedDate]))<>False))".




"Teamwork means never having to take all the blame yourself."

 

[Remou]

See also thread701-1475033

 

[regava]

Genomon - This query without the TOP 1 gives me correct data. I do not see how making your suggestion would improve anything. However, I gave it a try and I get error messages.

Remou - The thread you mention is my posting in a different forum that never gave me a hint as to how to solve this problem.

I do appreciate any help. Thank you.

 

[Remou]

I have posted in that thread. Did my suggestion nit suit?

 

[jrbarnett]

regava

By implementing the suggestions that genomon suggests, you get two enhancements:

1. The code becomes easier to read.
This makes it easier to debug.

2. The IsNull function is part of VBA. Therefore, for this to run, each row needs to be executed by VBA. This takes time, and slows down query execution. If your table has lots of rows of data, then this could significantly increase the total execution time.

By replacing "AND IsNull([tblTracking].[ClosedDate]))<>False" with "[tblTracking].[ClosedDate] IS NULL"

this is executed by the database engine without using VBA, which means that execution will be faster, as well as easier to understand.
Try it on a table with several hundred or thousand rows of data and you will see a big difference.

John

 

[Remou]

This problem has been solved, as far as I can see, by Golom in thread701-1475033.

 

[regava]

Thank you all for your help. It is highly appreciated. However, I want to make the following observations: 1) the code that I posted is the code generated by Access, 2) When I created the query I had the IS NULL as suggested and it was changed by Access.
As per Remou this problem was solved. Once again thanks to all you who took your time and help tremendously.