Message-ID:

<james> abuse me. I'm so lame I sent a bug report to debian-devel-changes -- Seen on #Debian

devel / comp.databases.ms-access / Problem with SQL query in Access

Problem with SQL query in Access

<ae91a0dd-f974-4de2-a4b1-15a6e1452254n@googlegroups.com>

https://www.novabbs.com/devel/article-flat.php?id=446&group=comp.databases.ms-access#446

copy link Newsgroups: comp.databases.ms-access

X-Received: by 2002:a37:59c7:: with SMTP id n190mr18186694qkb.146.1624191242668;
Sun, 20 Jun 2021 05:14:02 -0700 (PDT)
X-Received: by 2002:a5b:392:: with SMTP id k18mr27172827ybp.180.1624191242339;
Sun, 20 Jun 2021 05:14:02 -0700 (PDT)
Path: i2pn2.org!i2pn.org!weretis.net!feeder8.news.weretis.net!proxad.net!feeder1-2.proxad.net!209.85.160.216.MISMATCH!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups: comp.databases.ms-access
Date: Sun, 20 Jun 2021 05:14:02 -0700 (PDT)
Injection-Info: google-groups.googlegroups.com; posting-host=213.166.55.173; posting-account=sDyr7QoAAAA7hiaifqt-gaKY2K7OZ8RQ
NNTP-Posting-Host: 213.166.55.173
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <ae91a0dd-f974-4de2-a4b1-15a6e1452254n@googlegroups.com>
Subject: Problem with SQL query in Access
From: lutg...@icloud.com (Anon ymous)
Injection-Date: Sun, 20 Jun 2021 12:14:02 +0000
Content-Type: text/plain; charset="UTF-8"

by: Anon ymous - Sun, 20 Jun 2021 12:14 UTC

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

Subject	Author
Problem with SQL query in Access	Anon ymous