correct IIF function in query field

[Kaylen]

Can someone help me out with how to enter the correct expression in a field
of a query? I want the calculated field to return "N/A" if the result of a
formula is an error. The formula is

=DCount("[UM]![13]","UM","[UM]![13]='Y'")/(DCount("
[UM]![13]","UM","[UM]![13]='Y'")+DCount(" [UM]![13]","UM","[UM]![13]='N'"))

I want to put the correct expression in a parameter query. Do you think this
is possible?

 

[akphidelt]

Can you explain what you are trying to do here? The first expression looks
like it is just dividing an expression by the exact same expression. If you
want to check to see if the value of the Count is 0 then do something like

IIF(Dcount("13","UM","13='Y'")=0,"N/A",Dcount("13","UM","13='Y'")+Dcount("13","UM","13='N'"))

Remember Dcount is Dcount("fieldname","tablename","fieldname=")

If you explain a little more Ill be able to give you a better explanation.

 

[John Spencer]

What type of field is UM.13? Is it a yes/no (boolean) field? Or is it a
text field that actually contains text values of Y and N?

The only error you should get with this calculation is a Division by zero
error.

By the way 13 is a terrible name for a field. Anyway, the following
expression may work for you.

IIF(DCount("*","UM","[13] IN ('Y','N')" = 0, Null ,
DCount("*","UM","[13] ='Y')" / DCount("*","UM","[13] IN ('Y','N')")

If you want N/A instead of Null, then replace Null with "N/A"
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Kaylen]

Field 13 in the table is where there will be a Yes or No, or N/A answer. I
want to calculate the number of Yes in that field over the number of Yes and
No, excluding N/A. But the problem arises when all the data in field 13 is
N/A or blank, I get an error message. How can I make Access calculate the the
formula and return "N/A" if all the data in field 13 is an "N/A" instead of
returning an error message. Please help...

What type of field is UM.13? Is it a yes/no (boolean) field? Or is it a
text field that actually contains text values of Y and N?

The only error you should get with this calculation is a Division by zero
error.

By the way 13 is a terrible name for a field. Anyway, the following
expression may work for you.

IIF(DCount("*","UM","[13] IN ('Y','N')" = 0, Null ,
DCount("*","UM","[13] ='Y')" / DCount("*","UM","[13] IN ('Y','N')")

If you want N/A instead of Null, then replace Null with "N/A"
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Can someone help me out with how to enter the correct expression in a
field
of a query? I want the calculated field to return "N/A" if the result of a
formula is an error. The formula is

=DCount("[UM]![13]","UM","[UM]![13]='Y'")/(DCount("
[UM]![13]","UM","[UM]![13]='Y'")+DCount("
[UM]![13]","UM","[UM]![13]='N'"))

I want to put the correct expression in a parameter query. Do you think
this
is possible?

 

[Kaylen]

Let me explain the problem clearer. I have a table with fields headings in
numbers. The data in the fields is Y, N, or N/A. I want to calculate the
percentage of the count of Y over the count of Y and N, exclude N/A in the
denominator. But first I needed to filter out a certain selection in the
table only. That is why I made a query called "UM"( to filter out records
entered on certain date only). Now I want to calculate the number of Y/(N+Y)
based on data filtered by the query. I learned that you can't calculate if
the query has a parameter, or could you? The formula I use to calulate the Y
and N of field 13 in the query UM is

=DCount("[UM]![13]","UM","[UM]![13]='Y'")/(DCount("
[UM]![13]","UM","[UM]![13]='Y'")+DCount(" [UM]![13]","UM","[UM]![13]='N'"))

which works fine until all the data is if that field is "N/A", neither "Y'
or "N". Now I need a fomula that returns "N/A" if this is the case instead of
returning an error message. Please please help......

Field 13 in the table is where there will be a Yes or No, or N/A answer. I
want to calculate the number of Yes in that field over the number of Yes and
No, excluding N/A. But the problem arises when all the data in field 13 is
N/A or blank, I get an error message. How can I make Access calculate the the
formula and return "N/A" if all the data in field 13 is an "N/A" instead of
returning an error message. Please help...

What type of field is UM.13? Is it a yes/no (boolean) field? Or is it a
text field that actually contains text values of Y and N?

The only error you should get with this calculation is a Division by zero
error.

By the way 13 is a terrible name for a field. Anyway, the following
expression may work for you.

IIF(DCount("*","UM","[13] IN ('Y','N')" = 0, Null ,
DCount("*","UM","[13] ='Y')" / DCount("*","UM","[13] IN ('Y','N')")

If you want N/A instead of Null, then replace Null with "N/A"
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Can someone help me out with how to enter the correct expression in a
field
of a query? I want the calculated field to return "N/A" if the result of a
formula is an error. The formula is

=DCount("[UM]![13]","UM","[UM]![13]='Y'")/(DCount("
[UM]![13]","UM","[UM]![13]='Y'")+DCount("
[UM]![13]","UM","[UM]![13]='N'"))

I want to put the correct expression in a parameter query. Do you think
this
is possible?

 

[John Spencer]

Can I assume that you got an error with the recommended expression I
provided? If so, what was the error? Reviewing what I posted, I find
that I missed some parentheses. Here is the expression rewritten. It
seems to have the same number of left and right parentheses and they
seem to be in the correct places.


IIF(DCount("*","UM","[13] IN ('Y','N')") = 0, "N/A" ,
DCount("*","UM","[13] ='Y'") / DCount("*","UM","[13] IN ('Y','N')"))


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Kaylen]

John thank you sooo much!. The formula works....but with one problem. the
result is displayed in decimal and there is no option the field properties to
change it to percentage. I really need this number in percent form.....please
help me with this...

Can I assume that you got an error with the recommended expression I
provided? If so, what was the error? Reviewing what I posted, I find
that I missed some parentheses. Here is the expression rewritten. It
seems to have the same number of left and right parentheses and they
seem to be in the correct places.


IIF(DCount("*","UM","[13] IN ('Y','N')") = 0, "N/A" ,
DCount("*","UM","[13] ='Y'") / DCount("*","UM","[13] IN ('Y','N')"))


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Let me explain the problem clearer. I have a table with fields headings in
numbers. The data in the fields is Y, N, or N/A. I want to calculate the
percentage of the count of Y over the count of Y and N, exclude N/A in the
denominator. But first I needed to filter out a certain selection in the
table only. That is why I made a query called "UM"( to filter out records
entered on certain date only). Now I want to calculate the number of Y/(N+Y)
based on data filtered by the query. I learned that you can't calculate if
the query has a parameter, or could you? The formula I use to calulate the Y
and N of field 13 in the query UM is

=DCount("[UM]![13]","UM","[UM]![13]='Y'")/(DCount("
[UM]![13]","UM","[UM]![13]='Y'")+DCount(" [UM]![13]","UM","[UM]![13]='N'"))

which works fine until all the data is if that field is "N/A", neither "Y'
or "N". Now I need a fomula that returns "N/A" if this is the case instead of
returning an error message. Please please help......

 

[Kaylen]

Now I am thinking with all the experts in here, I am wondering if anyone can
help me with a formula that can calculate just the selective range of data of
a certain date. Based on initial problem I stated below, I have to make a
query that filter out only records of a certain date, then from that query I
use the IIf function to calculate the count of Y over the count of Y+N. Is
there a way or expression where I can eliminate having to create that query
and just calculated the percentage of (Y/Y+N) of all the records of a
selected date filtered from the table UM? Keep in mind that I still would
like the value to be "N/A" if there is no Y or N in the field of the selected
date. Any suggestion for the problem would be very much appreciated.

Let me explain the problem clearer. I have a table with fields headings in
numbers. The data in the fields is Y, N, or N/A. I want to calculate the
percentage of the count of Y over the count of Y and N, exclude N/A in the
denominator. But first I needed to filter out a certain selection in the
table only. That is why I made a query called "UM"( to filter out records
entered on certain date only). Now I want to calculate the number of Y/(N+Y)
based on data filtered by the query. I learned that you can't calculate if
the query has a parameter, or could you? The formula I use to calulate the Y
and N of field 13 in the query UM is

=DCount("[UM]![13]","UM","[UM]![13]='Y'")/(DCount("
[UM]![13]","UM","[UM]![13]='Y'")+DCount(" [UM]![13]","UM","[UM]![13]='N'"))

which works fine until all the data is if that field is "N/A", neither "Y'
or "N". Now I need a fomula that returns "N/A" if this is the case instead of
returning an error message. Please please help......

 

[John Spencer]

Well, you insisted on returning "N/A" if the result of test was zero. When
you did that then Access returns all values of the expression as a string.

Use

IIF(DCount("*","UM","[13] IN ('Y','N')") = 0, Null,
DCount("*","UM","[13] ='Y'") / DCount("*","UM","[13] IN ('Y','N')"))
This will return a number or null that can be formatted. Look up Format in
help - you can use the Format property of a control to show N/A if the
control's value is null. You can set the format property of a control to
0.00%;;;"N/A"
which will display null values as N/A

OR if you wish you can apply the format to the number in the calculation.
The number will then be a string, which can no longer be used in
calculations
IIF(DCount("*","UM","[13] IN ('Y','N')") = 0, "N/A" ,
Format( DCount("*","UM","[13] ='Y'") / DCount("*","UM","[13] IN
('Y','N')"),"Percent"))

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

John thank you sooo much!. The formula works....but with one problem. the
result is displayed in decimal and there is no option the field properties
to
change it to percentage. I really need this number in percent
form.....please
help me with this...

Can I assume that you got an error with the recommended expression I
provided? If so, what was the error? Reviewing what I posted, I find
that I missed some parentheses. Here is the expression rewritten. It
seems to have the same number of left and right parentheses and they
seem to be in the correct places.


IIF(DCount("*","UM","[13] IN ('Y','N')") = 0, "N/A" ,
DCount("*","UM","[13] ='Y'") / DCount("*","UM","[13] IN ('Y','N')"))


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Let me explain the problem clearer. I have a table with fields headings
in
numbers. The data in the fields is Y, N, or N/A. I want to calculate
the
percentage of the count of Y over the count of Y and N, exclude N/A in
the
denominator. But first I needed to filter out a certain selection in
the
table only. That is why I made a query called "UM"( to filter out
records
entered on certain date only). Now I want to calculate the number of
Y/(N+Y)
based on data filtered by the query. I learned that you can't calculate
if
the query has a parameter, or could you? The formula I use to calulate
the Y
and N of field 13 in the query UM is

=DCount("[UM]![13]","UM","[UM]![13]='Y'")/(DCount("
[UM]![13]","UM","[UM]![13]='Y'")+DCount("
[UM]![13]","UM","[UM]![13]='N'"))

which works fine until all the data is if that field is "N/A", neither
"Y'
or "N". Now I need a fomula that returns "N/A" if this is the case
instead of
returning an error message. Please please help......