IIF statement function criteria

[Dave]

The Sex field contains "M" and "F" and "X". The IIf statement will not show
any records when true. I would expect everything except "X". Does the IIF
statment not handle functions as one of the outcomes? Below is the sql from
the query:

SELECT Table1.Sex, *
FROM Table1
WHERE (((Table1.Sex)=IIf(1=1,Not ([Table1].[Sex])="X",[Sex])));

Thank you
Dave

 

[KARL DEWEY]

I would expect everything except "X".
Try this --
SELECT Table1.Sex, *
FROM Table1
WHERE [Table1].[Sex] <>"X";

 

[Dave]

The criteria needs to be prefixed with the IIF condition and the <>"X" is not
behaving like I'd expect it to.

Try this --
SELECT Table1.Sex, *
FROM Table1
WHERE [Table1].[Sex] <>"X";

The Sex field contains "M" and "F" and "X". The IIf statement will not show
any records when true. I would expect everything except "X". Does the IIF
statment not handle functions as one of the outcomes? Below is the sql from
the query:

SELECT Table1.Sex, *
FROM Table1
WHERE (((Table1.Sex)=IIf(1=1,Not ([Table1].[Sex])="X",[Sex])));

Thank you
Dave

 

[Klatuu]

Why do you think it has to have the IIf statement?
It appears you want to return the entire record, but you have one field at
the beginning. I'm not sure you need that.

I think
SELECT * FROM Table1 WHERE [Sex] <> "X";
Is all you really need.

The criteria needs to be prefixed with the IIF condition and the <>"X" is not
behaving like I'd expect it to.

I would expect everything except "X".

Try this --
SELECT Table1.Sex, *
FROM Table1
WHERE [Table1].[Sex] <>"X";

The Sex field contains "M" and "F" and "X". The IIf statement will not show
any records when true. I would expect everything except "X". Does the IIF
statment not handle functions as one of the outcomes? Below is the sql from
the query:

SELECT Table1.Sex, *
FROM Table1
WHERE (((Table1.Sex)=IIf(1=1,Not ([Table1].[Sex])="X",[Sex])));

Thank you
Dave

 

[John Spencer]

Can you describe what you want to have happen as if you were telling your
non-computer-literate grandmother?

Perhaps what you want is
SELECT Table1.Sex, *
FROM Table1
WHERE Table1.Sex=IIf(1=1,Not ([Table1].[Sex])="X",[Sex]))

If some statement is true then return M and F records, if the statement is
false then return all records (except those with no value - Null)

Where Table1.Sex <> IIF(TheTest,"X","Z")

 

[Dave]

I was trying to simplify the problem. The record return depends on: if
month(now())= 1. If its Janurary retreive all records exept "X", if not
January retreive all records.

Why do you think it has to have the IIf statement?
It appears you want to return the entire record, but you have one field at
the beginning. I'm not sure you need that.

I think
SELECT * FROM Table1 WHERE [Sex] <> "X";
Is all you really need.

The criteria needs to be prefixed with the IIF condition and the <>"X" is not
behaving like I'd expect it to.

I would expect everything except "X".
Try this --
SELECT Table1.Sex, *
FROM Table1
WHERE [Table1].[Sex] <>"X";


:

The Sex field contains "M" and "F" and "X". The IIf statement will not show
any records when true. I would expect everything except "X". Does the IIF
statment not handle functions as one of the outcomes? Below is the sql from
the query:

SELECT Table1.Sex, *
FROM Table1
WHERE (((Table1.Sex)=IIf(1=1,Not ([Table1].[Sex])="X",[Sex])));

Thank you
Dave

 

[KARL DEWEY]

Try this --

SELECT * FROM Table1 WHERE (((Table1.Sex) Not Like
IIf(Month(Date())=1,"C"))) OR (((Table1.Sex) Like IIf(Month(Date())<>1,"*")));

I was trying to simplify the problem. The record return depends on: if
month(now())= 1. If its Janurary retreive all records exept "X", if not
January retreive all records.

Why do you think it has to have the IIf statement?
It appears you want to return the entire record, but you have one field at
the beginning. I'm not sure you need that.

I think
SELECT * FROM Table1 WHERE [Sex] <> "X";
Is all you really need.

The criteria needs to be prefixed with the IIF condition and the <>"X" is not
behaving like I'd expect it to.



:

I would expect everything except "X".
Try this --
SELECT Table1.Sex, *
FROM Table1
WHERE [Table1].[Sex] <>"X";


:

The Sex field contains "M" and "F" and "X". The IIf statement will not show
any records when true. I would expect everything except "X". Does the IIF
statment not handle functions as one of the outcomes? Below is the sql from
the query:

SELECT Table1.Sex, *
FROM Table1
WHERE (((Table1.Sex)=IIf(1=1,Not ([Table1].[Sex])="X",[Sex])));

Thank you
Dave

 

[Dave]

The problem is that the statement:
WHERE Table1.Sex=IIf(1=1,Not ([Table1].[Sex])="X",[Sex]))
does not return any records. The "Not ([Table1].[Sex])="X"" is not being
treated in the IIF as it does when it is used soley as the criteria.

The statement below is not elegant, but it works (only for populated
records, nulls are another matter):

SELECT Table1.Sex, *
FROM Table1
WHERE (((Table1.Sex)=IIf(1=1,[sex],IIf([sex]="X","",[Sex]))));

Thank you for the help.

Dave

Can you describe what you want to have happen as if you were telling your
non-computer-literate grandmother?

Perhaps what you want is
SELECT Table1.Sex, *
FROM Table1
WHERE Table1.Sex=IIf(1=1,Not ([Table1].[Sex])="X",[Sex]))

If some statement is true then return M and F records, if the statement is
false then return all records (except those with no value - Null)

Where Table1.Sex <> IIF(TheTest,"X","Z")

The Sex field contains "M" and "F" and "X". The IIf statement will not
show
any records when true. I would expect everything except "X". Does the IIF
statment not handle functions as one of the outcomes? Below is the sql
from
the query:

SELECT Table1.Sex, *
FROM Table1
WHERE (((Table1.Sex)=IIf(1=1,Not ([Table1].[Sex])="X",[Sex])));

Thank you
Dave

 

[Klatuu]

IIf(You Provide All Criteria, You Get Good Answer, You Get Incomplete Answer)

I think you will need to add a calculated field to your query to get the
filtering like you want it:

Include: Iif(Month(Now()) = 1 And [Sex] IN("M", "F"), True, IIf
Month(Now()) <> 1, True, False))

Then put True in the Criteria

I was trying to simplify the problem. The record return depends on: if
month(now())= 1. If its Janurary retreive all records exept "X", if not
January retreive all records.

Why do you think it has to have the IIf statement?
It appears you want to return the entire record, but you have one field at
the beginning. I'm not sure you need that.

I think
SELECT * FROM Table1 WHERE [Sex] <> "X";
Is all you really need.

The criteria needs to be prefixed with the IIF condition and the <>"X" is not
behaving like I'd expect it to.



:

I would expect everything except "X".
Try this --
SELECT Table1.Sex, *
FROM Table1
WHERE [Table1].[Sex] <>"X";


:

The Sex field contains "M" and "F" and "X". The IIf statement will not show
any records when true. I would expect everything except "X". Does the IIF
statment not handle functions as one of the outcomes? Below is the sql from
the query:

SELECT Table1.Sex, *
FROM Table1
WHERE (((Table1.Sex)=IIf(1=1,Not ([Table1].[Sex])="X",[Sex])));

Thank you
Dave

 

[KARL DEWEY]

Maybe everyone is missing something. You have IIf(1=1,[sex],xxxx

Do you have a field named 1? Why 1=1 ?

The problem is that the statement:
WHERE Table1.Sex=IIf(1=1,Not ([Table1].[Sex])="X",[Sex]))
does not return any records. The "Not ([Table1].[Sex])="X"" is not being
treated in the IIF as it does when it is used soley as the criteria.

The statement below is not elegant, but it works (only for populated
records, nulls are another matter):

SELECT Table1.Sex, *
FROM Table1
WHERE (((Table1.Sex)=IIf(1=1,[sex],IIf([sex]="X","",[Sex]))));

Thank you for the help.

Dave

Can you describe what you want to have happen as if you were telling your
non-computer-literate grandmother?

Perhaps what you want is
SELECT Table1.Sex, *
FROM Table1
WHERE Table1.Sex=IIf(1=1,Not ([Table1].[Sex])="X",[Sex]))

If some statement is true then return M and F records, if the statement is
false then return all records (except those with no value - Null)

Where Table1.Sex <> IIF(TheTest,"X","Z")

The Sex field contains "M" and "F" and "X". The IIf statement will not
show
any records when true. I would expect everything except "X". Does the IIF
statment not handle functions as one of the outcomes? Below is the sql
from
the query:

SELECT Table1.Sex, *
FROM Table1
WHERE (((Table1.Sex)=IIf(1=1,Not ([Table1].[Sex])="X",[Sex])));

Thank you
Dave

 

[Klatuu]

He is giving us only a partial description of what he needs (again). The
original post said nothing about what is now 1=1.

When I asked why he needed an IIf (see OP), turns out he is looking for
January
Month(Now()) = 1 (his code, not mine)
I posted back the code that would work, which he chose to ignore.

Some people are just beyond help.

Maybe everyone is missing something. You have IIf(1=1,[sex],xxxx

Do you have a field named 1? Why 1=1 ?

The problem is that the statement:
WHERE Table1.Sex=IIf(1=1,Not ([Table1].[Sex])="X",[Sex]))
does not return any records. The "Not ([Table1].[Sex])="X"" is not being
treated in the IIF as it does when it is used soley as the criteria.

The statement below is not elegant, but it works (only for populated
records, nulls are another matter):

SELECT Table1.Sex, *
FROM Table1
WHERE (((Table1.Sex)=IIf(1=1,[sex],IIf([sex]="X","",[Sex]))));

Thank you for the help.

Dave

Can you describe what you want to have happen as if you were telling your
non-computer-literate grandmother?

Perhaps what you want is
SELECT Table1.Sex, *
FROM Table1
WHERE Table1.Sex=IIf(1=1,Not ([Table1].[Sex])="X",[Sex]))

If some statement is true then return M and F records, if the statement is
false then return all records (except those with no value - Null)

Where Table1.Sex <> IIF(TheTest,"X","Z")


The Sex field contains "M" and "F" and "X". The IIf statement will not
show
any records when true. I would expect everything except "X". Does the IIF
statment not handle functions as one of the outcomes? Below is the sql
from
the query:

SELECT Table1.Sex, *
FROM Table1
WHERE (((Table1.Sex)=IIf(1=1,Not ([Table1].[Sex])="X",[Sex])));

Thank you
Dave