Iff (1 Viewer)

[Gismo]

Hi all,

I want to use an Iff statement in a query
If "Dummy" = false, then I only want to see all the record with dummy as false else, if there are no dummy = false in the records set, just show me dummy = True
Basically I first want to filter dummy = false (10 records), if no, the I want to see the dummy = true (1 record)

My Iff statement does not give me the required result.

IIf([Material Data File]![Dummy]="0","0","-1")

 

[Gasman]

Well True and False are -1 and 0, not strings?
Is Dummy a String?

 

[Gismo]

Well True and False are -1 and 0, not strings?
Is Dummy a String?

Dummy is a yes or no tick box

 

[Gasman]

Dummy is a yes or no tick box

So again that would be 0 or -1?, so why are you using strings?

Why not just have the criteria as Dummy?

 

[Gismo]

So again that would be 0 or -1?, so why are you using strings?

Why not just have the criteria as Dummy?

I received an expression entered has invalid number when removing the " "

IIf([Material Data File]![Dummy]=0,0,-1)

I only want to see the Dummy = 0 if there are records with Dummy=0 else Dummy = -1

I tried the below but no filter has applied

IIf([Material Data File]![Dummy]=No,No,Yes)

 

[ebs17]

If-then-else cascades are always popular, especially if you're still very connected to Excel.

Why don't you query directly:

SELECT * FROM TableX
WHERE Dummy = True

Note:

Modern SQL: Three-Valued Logic (3VL) — Purpose, Benefits and Special Cases

Making decision despite missing SQL data (NULL): How to avoid OR…IS NULL in the WHERE clause.

modern-sql.com

Besides True (-1) and False (0), unknown (NULL) could also be present.

 

[Gismo]

If-then-else cascades are always popular, especially if you're still very connected to Excel.

Why don't you query directly:

SELECT * FROM TableX
WHERE Dummy = True

I need to see Dummy = -1 only if there are no Dummy=0 records

 

[GaP42]

Your data source contains records that are either dummy = true or dummy = false, then your query cannot automatically flip from showing only those records where dummy = true to then show dummy = false. You need to explicitly specify the criteria to use as suggested by ebs17. You could set up a control on the initiating form to pick the set you want.

 

[Josef P.]

[Material Data File]![Dummy] is a form control?

If "Dummy" = false, then I only want to see all the record with dummy as false else, if there are no dummy = false in the records set, just show me dummy = True
Basically I first want to filter dummy = false (10 records), if no, the I want to see the dummy = true (1 record)

Do you mean like this:
[Material Data File]![Dummy] = False ... show only Dummy=False records
a) [Material Data File]![Dummy] = True ... show all records. (Dummy=False or Dummy=true)
or
b) [Material Data File]![Dummy] = True ... show only Dummy=True records

 

[Gismo]

[Material Data File]![Dummy] is a form control?

Do you mean like this:
[Material Data File]![Dummy] = False ... show only Dummy=False records
[Material Data File]![Dummy] = True ... show all records. (Dummy=False or Dummy=true)

Yes this is a report
Your criteria is exactly what I need

 

[Josef P.]

..
where
    (
       YourDataSource.Dummy = Forms![Material Data File]![Dummy]
       OR
       Forms![Material Data File]![Dummy] = True
    )
    AND
    ...

... or open the report with the appropriate WhereCondition parameter. (I would prefer this variant, because I don't like form references in query.)

 

[Gismo]

..
where
    (
       YourDataSource.Dummy = Forms![Material Data File]![Dummy]
       OR
       Forms![Material Data File]![Dummy] = True
    )
    AND
    ...

... or open the report with the appropriate WhereCondition parameter. (I would prefer this variant, because I don't like form references in query.)

Still getting incorrect result

SELECT [Material Data File].Registration, [Material Data File].Sequence, [Material Data File].[IPC P/N], [Material Data File].Description, [Material Data File].QTY, [Material Data File].[SAP Material No], [Material Data File].Comment, [Warranty / Spares]![Warranty Description] & ": Rev:" & [Material Data File]![Revision] & ", CS Order: " & [Material Data File]![CS Order] & ", Notification: " & [Material Data File]![Notification] AS SAP, [Material Data File].[Warranty CS], [Material Data File].SparesCS, [Material Data File].Loaded, [Material Data File].Dummy
FROM [Material Data File] LEFT JOIN [Warranty / Spares] ON [Material Data File].[Warranty CS] = [Warranty / Spares].[Select]
WHERE ((([Material Data File].Registration)="111 AS350 B3e") AND (([Material Data File].Sequence)=5) AND (([Material Data File].Loaded)=True) AND (([Material Data File].Dummy)=[Material Data File]![Dummy] Or [Material Data File]![Dummy]=True));

 

[Josef P.]

[Material Data File] .. is the table and not a Form control?!

 

[Gismo]

[Material Data File] .. is the table and not a Form control?!

[Material Data File] is a table yes

 

[ebs17]

Somehow it all looks quite different from the first post.
Why don't you immediately describe the real and complete situation? Do thoughts and insights only come when you have written 10 posts times like this?
Where would your IIF cascade occur?

 

[Gismo]

Somehow it all looks quite different from the first post.
Why don't you immediately describe the real and complete situation? Do thoughts and insights only come when you have written 10 posts times like this?
Where would your IIF cascade occur?

My very first post requests all records to be displayed when dummy = 0, if no dummy = 0 then display only Dummy = -1
I used the iff statement as a field value and also tried in criteria

Each record with unique sequence will have a dummy record = 0 if there is a record with a dummy -1
Some records will only have a dummy = -1
So I need to see the Dummy = 0 first and filter out Dummy = -1
If there is no dummy = 0 then I need to see the Dummy = -1
Line number 2 is what I want to see only in this case

 

[Josef P.]

When do you think the value of a Yes/No data field in a record will not be the same when compared to itself?

...
where
    ...
    [Material Data File].Dummy=[Material Data File].[Dummy]

That is always true. You compare the value of the field with itself.

 

[ebs17]

I don't know if I understood that.

SELECT
   M.*
FROM
   [Material Data File] AS M
WHERE
   M.Dummy = NOT(
      (
         SELECT
            COUNT(*)
         FROM
            [Material Data File] AS X
         WHERE
            X.Sequenz = M.Sequenz
               AND
            X.Dummy = False
      )
   > 0)

 

[Gasman]

So you might need a Dcount to see if any 0 exist, not your dummy field?

 

[Gismo]

I don't know if I understood that.

SELECT
   M.*
FROM
   [Material Data File] AS M
WHERE
   M.Dummy = NOT(
      (
         SELECT
            COUNT(*)
         FROM
            [Material Data File] AS X
         WHERE
            X.Sequenz = M.Sequenz
               AND
            X.Dummy = False
      )
   > 0)

Thank you
This seems to work quite fine

Trying to use the same code on a form