IIF statement function criteria


G

Guest

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
 
Ad

Advertisements

G

Guest

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

Guest

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



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


Dave said:
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
 
G

Guest

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.

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



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


Dave said:
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
 
J

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")
 
G

Guest

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.


Klatuu said:
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.

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



KARL DEWEY said:
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
 
Ad

Advertisements

G

Guest

Try this --

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


Dave said:
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.


Klatuu said:
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.

Dave said:
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
 
G

Guest

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




John Spencer said:
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 said:
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
 
G

Guest

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

Dave said:
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.


Klatuu said:
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.

Dave said:
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
 
G

Guest

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

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

Dave said:
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




John Spencer said:
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 said:
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
 
Ad

Advertisements

G

Guest

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.

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

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

Dave said:
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




John Spencer said:
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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top