IIF(fldA = Fldb, 11, "Not 11").

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

RAther than write several queries that all do about the same thing I would
like to be able to specify criteria that would vary depending on the value of
a field ina form.

I have tried several variations of the IIF function such as
IIF(fldA = Fldb, 11, "Not 11").

I have included and removed "s till I am blue in the face but nothing sees
to work. I have no trouble with IIF(fldA = Fldb,11,12).
The problem is when I need to add text to the criteria.

Steve
 
Steve said:
RAther than write several queries that all do about the same thing I would
like to be able to specify criteria that would vary depending on the value of
a field ina form.

I have tried several variations of the IIF function such as
IIF(fldA = Fldb, 11, "Not 11").

I have included and removed "s till I am blue in the face but nothing sees
to work. I have no trouble with IIF(fldA = Fldb,11,12).
The problem is when I need to add text to the criteria.
Click to expand...


If you need to conditionally include the comparison
operator, then you should go into SQL view and edit the
WHERE clause so it looks like this:

IIf(FldA = Fldb, thefield = 11, thefield <> 11)

But I don't see how a control on a form fits into your
question.
 
Steve S said:
RAther than write several queries that all do about the same thing I would
like to be able to specify criteria that would vary depending on the value of
a field ina form.

I have tried several variations of the IIF function such as
IIF(fldA = Fldb, 11, "Not 11").

I have included and removed "s till I am blue in the face but nothing sees
to work. I have no trouble with IIF(fldA = Fldb,11,12).
The problem is when I need to add text to the criteria.

Steve
Click to expand...

Steve S,

Please provide the DDL (including CONSTRAINTS)
for all of the Tables involved. If the DDL is
not available, please include a well-formatted
text description (monospace-font) of the
relevant portions of the structures of each
Table, including a description of the Primary
and Foreign Keys (i.e. "relationships").
Please also include some sample data from each
(enough rows from each table to allow any
needed Queries to be executed). Please also
include the expected output.


Sincerely,

Chris O.
 
I have a one table, one query DB that I am using to test this problem. Is
there any way to send it to you? I tried your suggestion but the NOT portion
of the WHERE is still not recognized.

Steve
 
OK I have created a very small one table, one query DB to test this problem.
Can I send it to you.

The statement causing trouble is IIF(False,7, not 7)
What I want is when the expression of the IIF is TRUE ie. 4=4 I want all
occurrences of the value 7 to be returned by the query. When it it FALSE id
4=6 I want all other (not including 7) valuse returned.
 
RAther than write several queries that all do about the same thing I would
like to be able to specify criteria that would vary depending on the value of
a field ina form.

I have tried several variations of the IIF function such as
IIF(fldA = Fldb, 11, "Not 11").

I have included and removed "s till I am blue in the face but nothing sees
to work. I have no trouble with IIF(fldA = Fldb,11,12).
The problem is when I need to add text to the criteria.

Steve
Click to expand...

As Chris says, the problem is that you're trying to pass *an operator*
from the IIF statement. No can do. "Not 7" is actually taking the
one's complement of the binary representation of 7 and converting
0000000000000111 to 1111111111111000 - the NOT operator converts each
1 to a 0 and each 0 to a 1; and 1111111111111000 is how -8 is stored
in an integer field!

Instead, turn your logic around:

WHERE ([fldA] = [fldB] AND [yourfield] = 11) OR ([fldA] <> [fldB] AND
[yourfield] <> 11)


John W. Vinson[MVP]
 
Steve S said:
OK I have created a very small one table, one query DB to test this problem.
Can I send it to you.
Click to expand...

Like most people on usenet, I don't accept attachments, the security
risks are too great.
The statement causing trouble is IIF(False,7, not 7)
What I want is when the expression of the IIF is TRUE ie. 4=4 I want all
occurrences of the value 7 to be returned by the query. When it it FALSE id
4=6 I want all other (not including 7) valuse returned.
Click to expand...

IIF will not do what you have described.

The syntax is:

IIF(<condition>, return value if True, return value if False)

Open the Visual Basic Editor.
Go to: View>Immediate Window.

Type in: "? IIF(4 = 4, 7, not 7)", without the quotes, and press
enter.

The returned value is: 7.

Type in: "? IIF(4 = 5, 7, not 7)", without the quotes, and press
enter.

The returned value is, surprise: -8

What happened? "not" isn't enclosed by "", and so it isn't a
string. It also isn't numeric data. Access has *no* idea what to do
with that, so the results that come back might be anything.
Personally, I'm not sure how it arrived at the value -8.


Now, to move on.

SELECT *
FROM YourTable AS Y1
WHERE YourFld = IIF(FldA = FldB, 7, 8)

What happens to this? JET is passed this, and it compiles everything.
JET doesn't know what the results of the IIF function will be, but it
sets things up to receive a value. That value *must* be of the same
datatype (or must be convertible by the engine at runtime) as YourFld.

What you are trying to do is get the IIF to return an *operator* such
as NOT, =, <>, or whatever, and have that operator *become* a part of
the SQL statement. I'm sorry, but it can't do that. The IIF function
*returns* a value, it does not modify the SQL statement itself.


Sincerely,

Chris O.
 
What happened? "not" isn't enclosed by "", and so it isn't a
string. It also isn't numeric data. Access has *no* idea what to do
with that, so the results that come back might be anything.
Personally, I'm not sure how it arrived at the value -8.
Click to expand...

Correction: *Not* is being treated as a bit-wise operator. (Thank
you, John Vinson, for pointing that out.) <raps self on head sharply
/>


Sincerely,

Chris O.
 
Thanks to both you and Chris for explaining why what I was trying to do will
not work. However there must be some way to code the Criteria line of the
select query to accomplish the task. I tried your suggestion of changing my
logic and I agree in principal but can't seem to make it work.

If Criteria is TRUE I want to return only a small subset of records. If
Criteria is FALSE I want to return all others (NOT including those returned
if TRUE).

There has to be some way to do this. I am not proficient in SQL so would
prefer a solution that can be coded in the criteria line.

thanks for all the help.

John Vinson said:
RAther than write several queries that all do about the same thing I would
like to be able to specify criteria that would vary depending on the value of
a field ina form.

I have tried several variations of the IIF function such as
IIF(fldA = Fldb, 11, "Not 11").

I have included and removed "s till I am blue in the face but nothing sees
to work. I have no trouble with IIF(fldA = Fldb,11,12).
The problem is when I need to add text to the criteria.

Steve
Click to expand...

As Chris says, the problem is that you're trying to pass *an operator*
from the IIF statement. No can do. "Not 7" is actually taking the
one's complement of the binary representation of 7 and converting
0000000000000111 to 1111111111111000 - the NOT operator converts each
1 to a 0 and each 0 to a 1; and 1111111111111000 is how -8 is stored
in an integer field!

Instead, turn your logic around:

WHERE ([fldA] = [fldB] AND [yourfield] = 11) OR ([fldA] <> [fldB] AND
[yourfield] <> 11)


John W. Vinson[MVP]
Click to expand...
 
Chris,
Please look at my latest post to John.

Chris2 said:
Correction: *Not* is being treated as a bit-wise operator. (Thank
you, John Vinson, for pointing that out.) <raps self on head sharply
/>


Sincerely,

Chris O.
Click to expand...
 
If Criteria is TRUE I want to return only a small subset of records. If
Criteria is FALSE I want to return all others (NOT including those returned
if TRUE).

There has to be some way to do this. I am not proficient in SQL so would
prefer a solution that can be coded in the criteria line.
Click to expand...

Ok, then it's even simpler: on two successive criteria lines put

<True/False Expression> AND Criterion>
<True/False Expression> = False

The SQL would be (frex)

WHERE ([FldA] = [FldB] AND [FldX] = 11) OR ([FldA] = [FldB]) = False

or equivalently

WHERE ([FldA] = [FldB] AND [FldX] = 11) OR ([FldA] <> [FldB])


John W. Vinson[MVP]
 
It Works --Kind Of
Following your sugggestion I coded the criteria as:

Criteria: (Forms]![Score Sheets]![Combo0]=11) AND (=11)
Or: (Forms]![Score Sheets]![Combo0]<>11) AND (<>11)

and this worked just fine. After I closed and reopend the query I saw that
SQL compiler/converter had made several changes to my query grid. In the
colum where I had entered the above code it was changed to:

Criteria: 11
Or: <>11

it also added a column with this code:

Field name: [Forms]![Score Sheets]![Combo0]

Criteria: 11
Or: <>11

I never thought of entering a field name that was not on the table or query
I was using as input.

Thanks again

John Vinson said:
If Criteria is TRUE I want to return only a small subset of records. If
Criteria is FALSE I want to return all others (NOT including those returned
if TRUE).

There has to be some way to do this. I am not proficient in SQL so would
prefer a solution that can be coded in the criteria line.
Click to expand...

Ok, then it's even simpler: on two successive criteria lines put

<True/False Expression> AND Criterion>
<True/False Expression> = False

The SQL would be (frex)

WHERE ([FldA] = [FldB] AND [FldX] = 11) OR ([FldA] = [FldB]) = False

or equivalently

WHERE ([FldA] = [FldB] AND [FldX] = 11) OR ([FldA] <> [FldB])


John W. Vinson[MVP]
Click to expand...
 
Back
Top