CODEWITHSUNDEEP
sqlms-access
rohrl77
rohrl77

Reputation: 3337

Why does this access query work and the other does not?

I have a query that filters based on a boolean value on a form (Task Archived). The boolean value is stored as a long in the underlying table (only 0 and -1 are allowed).

I wanted to write a query that would either show only those records that are false (records not archived) or all records.

I would have expected the follwing query to work, but it does not... it returns only those records for which the archive field is set to True (-1):

SELECT tbl_Tasks.TaskID, tbl_Tasks.TArchive
FROM tbl_Tasks
WHERE (((tbl_Tasks.TArchive)=
    IIf([Forms]![frm_Activity]![txtArchivedTaskDisplay]=0,False,
    (tbl_Tasks.TArchive)=0 Or (tbl_Tasks.TArchive)=-1)));

The query below, which I would expect to show only those records that are True, in fact returns all records:

SELECT tbl_Tasks.TaskID, tbl_Tasks.TArchive
FROM tbl_Tasks
WHERE (((tbl_Tasks.TArchive)=
    IIf([Forms]![frm_Activity]![txtArchivedTaskDisplay]=0,False,
    (tbl_Tasks.TArchive)<>0)));

Why is this? What logic is access follwing here?

Upvotes: 0

Views: 39

Answers (1)

Krish
Krish

Reputation: 5917

You cannot put SQL where condition within IIF. Anything within IIF will be evaluated if possible. Since you only have 0 or -1 below evaluation will always be true. ((tbl_Tasks.TArchive)=0 Or (tbl_Tasks.TArchive)=-1) => true

that's why you are only seeing TArchive = true

You could try something like this for your first query:

WHERE 
tbl_Tasks.TArchive **<** IIf(displayArchivedOnly,0,1);

for the second one:

WHERE 
    tbl_Tasks.TArchive = [Forms]![frm_Activity]![txtArchivedTaskDisplay]

assuming your txtArchivedTaskDisplay holds either true or false value like a toggle button. but again, read more about how iif works.

Upvotes: 1

Related Questions