iif with yes, no, and null values

[Guest]

At Risk?: IIf([Result]>=[75th],Yes,No) - This works fine in my query

I discovered though that I am getting null values in [75th] which returned
"No" in [At Risk?] so I changed expression:

At Risk?: IIf([Result]>=[75th],Yes,iif([75th]=Null, "Age Out Of
Parameters",No)) - Which does not work

At Risk should = "Yes" OR "No" OR "Age Out Of Parameters"

Can anyone offer a solution? Thank you.

 

[Roger Carlson]

You cannot use the equal sign (=) with NULL. Null is not equal to anything
including Null. This expression: Null=Null will evaluate as FALSE. To test
for a Null value, you need to use the IsNull() function. So try this:

At Risk?: IIf([Result]>=[75th],Yes,iif(IsNull([75th]), "Age Out Of >
Parameters",No))

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[BruceM]

I'm not exactly sure what is going on with your various fields and data
types, but one thing I notice is that Yes and No are not in quotes. I don't
know if a calculated query field can be Yes/No, but even if it can, the
third option eliminates that possibility.
Perhaps you will need to use "" instead of Null. There's not a lot of
information in your question.
By the way, I think a question mark is OK in a field name, but I also
believe you would be doing yourself a favor if you limited field names to
alphanumeric characters, and save other characters for labels and text
strings.

 

[Wayne Morgan]

Null isn't a value, it is the absence of a value, so you can't test to see
if you're equal to it.

At Risk?: IIf([Result]>=[75th],Yes,IIf([75th] Is Null, "Age Out Of
Parameters",No))
or
At Risk?: IIf([Result]>=[75th],Yes,IIf(IsNull([75th]), "Age Out Of
Parameters",No))

Also, I don't recommend the question mark in the field name.

 

[BruceM]

Oops. Forgot about that =Null thing, as Roger pointed out.

I'm not exactly sure what is going on with your various fields and data
types, but one thing I notice is that Yes and No are not in quotes. I
don't know if a calculated query field can be Yes/No, but even if it can,
the third option eliminates that possibility.
Perhaps you will need to use "" instead of Null. There's not a lot of
information in your question.
By the way, I think a question mark is OK in a field name, but I also
believe you would be doing yourself a favor if you limited field names to
alphanumeric characters, and save other characters for labels and text
strings.

At Risk?: IIf([Result]>=[75th],Yes,No) - This works fine in my query

I discovered though that I am getting null values in [75th] which
returned
"No" in [At Risk?] so I changed expression:

At Risk?: IIf([Result]>=[75th],Yes,iif([75th]=Null, "Age Out Of
Parameters",No)) - Which does not work

At Risk should = "Yes" OR "No" OR "Age Out Of Parameters"

Can anyone offer a solution? Thank you.

 

[BruceM]

In both your reply and Roger's reply there are no quote marks around the Yes
and No. If they aren't necessary, could you explain why? I had thought
that to see the word Yes or No in the field they would need to have quote
marks around them, same as for "Age Out Of Parameters".

Null isn't a value, it is the absence of a value, so you can't test to see
if you're equal to it.

At Risk?: IIf([Result]>=[75th],Yes,IIf([75th] Is Null, "Age Out Of
Parameters",No))
or
At Risk?: IIf([Result]>=[75th],Yes,IIf(IsNull([75th]), "Age Out Of
Parameters",No))

Also, I don't recommend the question mark in the field name.

--
Wayne Morgan
MS Access MVP

At Risk?: IIf([Result]>=[75th],Yes,No) - This works fine in my query

I discovered though that I am getting null values in [75th] which
returned
"No" in [At Risk?] so I changed expression:

At Risk?: IIf([Result]>=[75th],Yes,iif([75th]=Null, "Age Out Of
Parameters",No)) - Which does not work

At Risk should = "Yes" OR "No" OR "Age Out Of Parameters"

Can anyone offer a solution? Thank you.

 

[Wayne Morgan]

The main reason is probably because we copied and pasted what the user had
and just replaced the part dealing with the Null. However, it is possible
that Access is accepting Yes and No as True and False since these are
possible formats for a True/False field.

 

[Guest]

Thank you guys for the solution, I haven't worked with null values much
before. The Yes/No works in my query without quotes because the filed
properties are formated as a Yes/No.

In both your reply and Roger's reply there are no quote marks around the Yes
and No. If they aren't necessary, could you explain why? I had thought
that to see the word Yes or No in the field they would need to have quote
marks around them, same as for "Age Out Of Parameters".

Null isn't a value, it is the absence of a value, so you can't test to see
if you're equal to it.

At Risk?: IIf([Result]>=[75th],Yes,IIf([75th] Is Null, "Age Out Of
Parameters",No))
or
At Risk?: IIf([Result]>=[75th],Yes,IIf(IsNull([75th]), "Age Out Of
Parameters",No))

Also, I don't recommend the question mark in the field name.

--
Wayne Morgan
MS Access MVP

At Risk?: IIf([Result]>=[75th],Yes,No) - This works fine in my query

I discovered though that I am getting null values in [75th] which
returned
"No" in [At Risk?] so I changed expression:

At Risk?: IIf([Result]>=[75th],Yes,iif([75th]=Null, "Age Out Of
Parameters",No)) - Which does not work

At Risk should = "Yes" OR "No" OR "Age Out Of Parameters"

Can anyone offer a solution? Thank you.

 

[BruceM]

There is much I have yet to understand. This one has me baffled,
particularly that a Yes/No field could be > or < another field. Still,
thanks for the response.

Thank you guys for the solution, I haven't worked with null values much
before. The Yes/No works in my query without quotes because the filed
properties are formated as a Yes/No.

In both your reply and Roger's reply there are no quote marks around the
Yes
and No. If they aren't necessary, could you explain why? I had thought
that to see the word Yes or No in the field they would need to have quote
marks around them, same as for "Age Out Of Parameters".

message

Null isn't a value, it is the absence of a value, so you can't test to
see
if you're equal to it.

At Risk?: IIf([Result]>=[75th],Yes,IIf([75th] Is Null, "Age Out Of
Parameters",No))
or
At Risk?: IIf([Result]>=[75th],Yes,IIf(IsNull([75th]), "Age Out Of
Parameters",No))

Also, I don't recommend the question mark in the field name.

--
Wayne Morgan
MS Access MVP


At Risk?: IIf([Result]>=[75th],Yes,No) - This works fine in my query

I discovered though that I am getting null values in [75th] which
returned
"No" in [At Risk?] so I changed expression:

At Risk?: IIf([Result]>=[75th],Yes,iif([75th]=Null, "Age Out Of
Parameters",No)) - Which does not work

At Risk should = "Yes" OR "No" OR "Age Out Of Parameters"

Can anyone offer a solution? Thank you.

 

[Guest]

This is the expression I am using now thanks to your help:

At Risk?: IIf([Result]>=[75th],"Yes",IIf([75th] Is Null,"Age Out Of
Parameters","No"))

Because the field [At Risk?] was no longer a Yes/No format in the query I
changed it to text requiring the " " before and after the text answers I
wanted to use.


This is in a query with a one sided join between two tables. One table is a
set of constant ratios, lab values to age in hours. The other table has all
gathered lab values in the hospital.

If the lab value at a given age in hours in table 1 is > or = the constant
value in table 2 the test value is flagged with a yes. My report is
conditionally formatted to return in red any yes values. Lab values drawn
at the age of <12 hours or >124 hours are not plotted and return a null value.

The concept of IS NULL rather than =Null didn't occur to me but you guys
helped me with that.

Thanks again.

There is much I have yet to understand. This one has me baffled,
particularly that a Yes/No field could be > or < another field. Still,
thanks for the response.

Thank you guys for the solution, I haven't worked with null values much
before. The Yes/No works in my query without quotes because the filed
properties are formated as a Yes/No.

In both your reply and Roger's reply there are no quote marks around the
Yes
and No. If they aren't necessary, could you explain why? I had thought
that to see the word Yes or No in the field they would need to have quote
marks around them, same as for "Age Out Of Parameters".

message
Null isn't a value, it is the absence of a value, so you can't test to
see
if you're equal to it.

At Risk?: IIf([Result]>=[75th],Yes,IIf([75th] Is Null, "Age Out Of
Parameters",No))
or
At Risk?: IIf([Result]>=[75th],Yes,IIf(IsNull([75th]), "Age Out Of
Parameters",No))

Also, I don't recommend the question mark in the field name.

--
Wayne Morgan
MS Access MVP


At Risk?: IIf([Result]>=[75th],Yes,No) - This works fine in my query

I discovered though that I am getting null values in [75th] which
returned
"No" in [At Risk?] so I changed expression:

At Risk?: IIf([Result]>=[75th],Yes,iif([75th]=Null, "Age Out Of
Parameters",No)) - Which does not work

At Risk should = "Yes" OR "No" OR "Age Out Of Parameters"

Can anyone offer a solution? Thank you.

 

[BruceM]

I get it! I appreciate hearing back from you. Glad I could help, after all
that.

This is the expression I am using now thanks to your help:

At Risk?: IIf([Result]>=[75th],"Yes",IIf([75th] Is Null,"Age Out Of
Parameters","No"))

Because the field [At Risk?] was no longer a Yes/No format in the query I
changed it to text requiring the " " before and after the text answers I
wanted to use.


This is in a query with a one sided join between two tables. One table is
a
set of constant ratios, lab values to age in hours. The other table has
all
gathered lab values in the hospital.

If the lab value at a given age in hours in table 1 is > or = the constant
value in table 2 the test value is flagged with a yes. My report is
conditionally formatted to return in red any yes values. Lab values
drawn
at the age of <12 hours or >124 hours are not plotted and return a null
value.

The concept of IS NULL rather than =Null didn't occur to me but you guys
helped me with that.

Thanks again.

There is much I have yet to understand. This one has me baffled,
particularly that a Yes/No field could be > or < another field. Still,
thanks for the response.

Thank you guys for the solution, I haven't worked with null values much
before. The Yes/No works in my query without quotes because the filed
properties are formated as a Yes/No.

:

In both your reply and Roger's reply there are no quote marks around
the
Yes
and No. If they aren't necessary, could you explain why? I had
thought
that to see the word Yes or No in the field they would need to have
quote
marks around them, same as for "Age Out Of Parameters".

message
Null isn't a value, it is the absence of a value, so you can't test
to
see
if you're equal to it.

At Risk?: IIf([Result]>=[75th],Yes,IIf([75th] Is Null, "Age Out Of
Parameters",No))
or
At Risk?: IIf([Result]>=[75th],Yes,IIf(IsNull([75th]), "Age Out Of
Parameters",No))

Also, I don't recommend the question mark in the field name.

--
Wayne Morgan
MS Access MVP


At Risk?: IIf([Result]>=[75th],Yes,No) - This works fine in my
query

I discovered though that I am getting null values in [75th] which
returned
"No" in [At Risk?] so I changed expression:

At Risk?: IIf([Result]>=[75th],Yes,iif([75th]=Null, "Age Out Of
Parameters",No)) - Which does not work

At Risk should = "Yes" OR "No" OR "Age Out Of Parameters"

Can anyone offer a solution? Thank you.