From Newsgroup: comp.databases.ms-sqlserv

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.

Thanks

Laurent
--- Synchronet 3.21d-Linux NewsLink 1.2