I'm working on a SELECT statement that filters a query made of two inner joined tables. The query returns more than a million and half records without any filter.
Both tables are linked tables from sql server.
When I open the query, it's very fast. I can see all the records in a blink of an eye.
When I add filters to this query, it's still very fast. No lag time at all.
My Problem :
The moment I add NZ(UnitPrice,0)>0 as a filter, it takes God knows how long to show the result. I wasn't patient enough to wait and I had to force Access to shut down.
If I change my filter to UnitPrice=0 or UnitPrice>0 the query still is perfect.
UnitPrice contains null values for almost half of the records.
Is there any way to have a high perfomance and still use NZ in a query?
Thanks
Both tables are linked tables from sql server.
When I open the query, it's very fast. I can see all the records in a blink of an eye.
When I add filters to this query, it's still very fast. No lag time at all.
My Problem :
The moment I add NZ(UnitPrice,0)>0 as a filter, it takes God knows how long to show the result. I wasn't patient enough to wait and I had to force Access to shut down.
If I change my filter to UnitPrice=0 or UnitPrice>0 the query still is perfect.
UnitPrice contains null values for almost half of the records.
Is there any way to have a high perfomance and still use NZ in a query?
SQL:
SELECT
B_ID, OrderPK, OrderNo, Delivery
FROM
qryOrders AS Q
WHERE
q.Delivery<#2023/06/01# AND
q.OrderedFromFK=2 AND
Nz(q.UnitPrice,0)=0 AND
q.Deleted=False
ORDER BY
Delivery ASC, Shipping ASC