From Anon ymous@21:1/5 to All on Sun Jun 20 05:14:02 2021
Hi
I have an SQL query in Access which doesn't work as I expected. It returns more results than it should.
The problem is that the field "ExpiryActionDoneDate" can be NULL when the reagent is still in use. In this case I want to search with the "Expires" field which is also a date.
The query returns way more results (43) than when I only use the field "ExpiryActionDoneDate" (1).
SELECT *
FROM Reagent_tbl
WHERE IIF(ExpiryActionDoneDate IS NOT NULL,
(DateScanned <= #2021-05-15# AND ExpiryActionDoneDate <= #2021-05-15#), (DateScanned <= #2021-05-15# AND Expires <= #2021-05-15#))
AND Reagent_tbl.FKIDReagentConfig
IN
(SELECT Sample_Types_Medium_Config.FKIDReagentConfig
FROM Sample_Types_Medium_Config
WHERE FKIDSampleTypesConfig =
( SELECT Sample_Types_Config.ID
FROM Sample_Types_Config
WHERE SampleTypeNames = 'BB'))
ORDER BY ProductName, DateScanned ASC
The first result returned has an empty "ExpiryActionDoneDate" field so the query switches to the other field.
What I don't understand: The IIF, is it evaluated once or for every record?
Is that what I am trying to do feasible?
Or is there an other way to get the result I want.
The only other idea floating around my head would be two "Select" combined with an "or" with the 2 dates but no sure if that would work any better.
I hope the GougleGroups webinterface hasn't too much mangled the formatting.