DCount and IIf function

[Kaylen]

I really need help on this. I want a field in a query to calculate the
pecentage of the count "Y" over the sum of "Y" and "N". The formula is use
for this is:
=DCount("[UM]![5]","UM","[UM]![5]='Y'")/(DCount("
[UM]![5]","UM","[UM]![5]='Y'")+DCount(" [UM]![5]","UM","[UM]![5]='N'"))

But when all my data for that column is "N/A", I get the error message in
the query:

"This expression is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables." when I run the query.

If I try to put that same formula into a control source of a text box, I get
the return value of #NUM!. I guess I want the formula to works but if none
the data in the feild is a "Y" or "N"" then I want it to return "N/A" instead
of error messages. Any advice on this problem quick is very much appreciated.

 

[akphidelt]

I tried to answer your other question but obviously you haven't read it. Go
to tools-->macros-->Visual Basic Editor

Click on View then click on Immediate Window

In that window type in

?DCount("[UM]![5]","UM","[UM]![5]='Y'")

Don't forget the question mark
Play around with that til you get it to accurately count that table.

You are completely all over the place with this. And read your other posts.

 

[Kaylen]

First I want to let you know how much I appreciate your effort to help. Sorry
I am quite new to Access 2003 so your help in details would be very helpful
to me. I tried what you suggest but it still wouldn't work. I want to use
this formula to calculate the percentage:

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

meaning the count of Y in UM query field 13/ (count of Y+N in UM query field
13)

This formula works but when field 13 has all "N/A" and not a Y or N, there
is an error message of #Num!. I want the formula to return a "N/A" if that is
the case instead of #Num! So do you think I should use the IIf function? if
so, what will it be? Please help....

I tried to answer your other question but obviously you haven't read it. Go
to tools-->macros-->Visual Basic Editor

Click on View then click on Immediate Window

In that window type in

?DCount("[UM]![5]","UM","[UM]![5]='Y'")

Don't forget the question mark
Play around with that til you get it to accurately count that table.

You are completely all over the place with this. And read your other posts.

I really need help on this. I want a field in a query to calculate the
pecentage of the count "Y" over the sum of "Y" and "N". The formula is use
for this is:
=DCount("[UM]![5]","UM","[UM]![5]='Y'")/(DCount("
[UM]![5]","UM","[UM]![5]='Y'")+DCount(" [UM]![5]","UM","[UM]![5]='N'"))

But when all my data for that column is "N/A", I get the error message in
the query:

"This expression is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables." when I run the query.

If I try to put that same formula into a control source of a text box, I get
the return value of #NUM!. I guess I want the formula to works but if none
the data in the feild is a "Y" or "N"" then I want it to return "N/A" instead
of error messages. Any advice on this problem quick is very much appreciated.

 

[BruceM]

If there is neither Y nor N, the divisor is 0, which is why you are getting
the error. You can simplify the DCount syntax, and allow for a zero for the
N count:
=IIf(DCount("*","[UM]","[13] = 'N/A'") =
DCount("*","[UM]"),0,DCount("*","[UM]","[13] = 'Y'")/DCount("*","[UM]","[13]
= 'Y'") + DCount("*","[UM]","[13] = 'N'"))
That is, if the number of records with "N/A" equals the total number of
records, return a 0, otherwise go ahead with the calculation.

First I want to let you know how much I appreciate your effort to help.
Sorry
I am quite new to Access 2003 so your help in details would be very
helpful
to me. I tried what you suggest but it still wouldn't work. I want to use
this formula to calculate the percentage:

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

meaning the count of Y in UM query field 13/ (count of Y+N in UM query
field
13)

This formula works but when field 13 has all "N/A" and not a Y or N, there
is an error message of #Num!. I want the formula to return a "N/A" if that
is
the case instead of #Num! So do you think I should use the IIf function?
if
so, what will it be? Please help....

I tried to answer your other question but obviously you haven't read it.
Go
to tools-->macros-->Visual Basic Editor

Click on View then click on Immediate Window

In that window type in

?DCount("[UM]![5]","UM","[UM]![5]='Y'")

Don't forget the question mark
Play around with that til you get it to accurately count that table.

You are completely all over the place with this. And read your other
posts.

I really need help on this. I want a field in a query to calculate the
pecentage of the count "Y" over the sum of "Y" and "N". The formula is
use
for this is:
=DCount("[UM]![5]","UM","[UM]![5]='Y'")/(DCount("
[UM]![5]","UM","[UM]![5]='Y'")+DCount(" [UM]![5]","UM","[UM]![5]='N'"))

But when all my data for that column is "N/A", I get the error message
in
the query:

"This expression is typed incorrectly, or it is too complex to be
evaluated.
For example, a numeric expression may contain too many complicated
elements.
Try simplifying the expression by assigning parts of the expression to
variables." when I run the query.

If I try to put that same formula into a control source of a text box,
I get
the return value of #NUM!. I guess I want the formula to works but if
none
the data in the feild is a "Y" or "N"" then I want it to return "N/A"
instead
of error messages. Any advice on this problem quick is very much
appreciated.

 

[Kaylen]

I tried the formula, it returns a 0% when all the data is "N/A" but it was
incorrect when the data is not "N/A". 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......

If there is neither Y nor N, the divisor is 0, which is why you are getting
the error. You can simplify the DCount syntax, and allow for a zero for the
N count:
=IIf(DCount("*","[UM]","[13] = 'N/A'") =
DCount("*","[UM]"),0,DCount("*","[UM]","[13] = 'Y'")/DCount("*","[UM]","[13]
= 'Y'") + DCount("*","[UM]","[13] = 'N'"))
That is, if the number of records with "N/A" equals the total number of
records, return a 0, otherwise go ahead with the calculation.

First I want to let you know how much I appreciate your effort to help.
Sorry
I am quite new to Access 2003 so your help in details would be very
helpful
to me. I tried what you suggest but it still wouldn't work. I want to use
this formula to calculate the percentage:

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

meaning the count of Y in UM query field 13/ (count of Y+N in UM query
field
13)

This formula works but when field 13 has all "N/A" and not a Y or N, there
is an error message of #Num!. I want the formula to return a "N/A" if that
is
the case instead of #Num! So do you think I should use the IIf function?
if
so, what will it be? Please help....

I tried to answer your other question but obviously you haven't read it.
Go
to tools-->macros-->Visual Basic Editor

Click on View then click on Immediate Window

In that window type in

?DCount("[UM]![5]","UM","[UM]![5]='Y'")

Don't forget the question mark
Play around with that til you get it to accurately count that table.

You are completely all over the place with this. And read your other
posts.

:

I really need help on this. I want a field in a query to calculate the
pecentage of the count "Y" over the sum of "Y" and "N". The formula is
use
for this is:
=DCount("[UM]![5]","UM","[UM]![5]='Y'")/(DCount("
[UM]![5]","UM","[UM]![5]='Y'")+DCount(" [UM]![5]","UM","[UM]![5]='N'"))

But when all my data for that column is "N/A", I get the error message
in
the query:

"This expression is typed incorrectly, or it is too complex to be
evaluated.
For example, a numeric expression may contain too many complicated
elements.
Try simplifying the expression by assigning parts of the expression to
variables." when I run the query.

If I try to put that same formula into a control source of a text box,
I get
the return value of #NUM!. I guess I want the formula to works but if
none
the data in the feild is a "Y" or "N"" then I want it to return "N/A"
instead
of error messages. Any advice on this problem quick is very much
appreciated.

 

[Guillermo_Lopez]

I tried the formula, it returns a 0% when all the data is "N/A" but it was
incorrect when the data is not "N/A". 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 insteadof
returning an error message. Please please help......

If there is neither Y nor N, the divisor is 0, which is why you are getting
the error.  You can simplify the DCount syntax, and allow for a zero for the
N count:
=IIf(DCount("*","[UM]","[13] = 'N/A'") =
DCount("*","[UM]"),0,DCount("*","[UM]","[13] = 'Y'")/DCount("*","[UM]","[13]
= 'Y'") + DCount("*","[UM]","[13] = 'N'"))
That is, if the number of records with "N/A" equals the total number of
records, return a 0, otherwise go ahead with the calculation.

First I want to let you know how much I appreciate your effort to help..
Sorry
I am quite new to Access 2003 so your help in details would be very
helpful
to me. I tried what you suggest but it still wouldn't work. I want to use
this formula to calculate the percentage:
=DCount("[UM]![13]","UM","[UM]![13]='Y'")/(DCount("
[UM]![13]","UM","[UM]![13]='Y'")+DCount("
[UM]![13]","UM","[UM]![13]='N'"))
meaning the count of Y in UM query field 13/ (count of Y+N in UM query
field
13)
This formula works but when field 13 has all "N/A" and not a Y or N, there
is an error message of #Num!. I want the formula to return a "N/A" if that
is
the case instead of #Num!  So do you think I should use the IIf function?
if
so, what will it be? Please help....
:
I tried to answer your other question but obviously you haven't read it.
Go
to tools-->macros-->Visual Basic Editor
Click on View then click on Immediate Window
In that window type in
?DCount("[UM]![5]","UM","[UM]![5]='Y'")
Don't forget the question mark
Play around with that til you get it to accurately count that table.
You are completely all over the place with this. And read your other
posts.
:
I really need help on this. I want a field in a query to calculate the
pecentage of the count "Y" over the sum of "Y" and "N". The formulais
use
for this is:
      =DCount("[UM]![5]","UM","[UM]![5]='Y'")/(DCount("
[UM]![5]","UM","[UM]![5]='Y'")+DCount(" [UM]![5]","UM","[UM]![5]='N'"))
 But when all my data for that column is "N/A", I get the error message
in
the query:
"This expression is typed incorrectly, or it is too complex to be
evaluated.
For example, a numeric expression may contain too many complicated
elements.
Try simplifying the expression by assigning parts of the expressionto
variables." when I run the query.
If I try to put that same formula into a control source of a text box,
I get
the return value of #NUM!. I guess I want the formula to works but if
none
the data in the feild is a "Y" or "N"" then I want it to return "N/A"
instead
of error messages. Any advice on this problem quick is very much
appreciated.- Hide quoted text -

- Show quoted text -

Try this:

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

- GL

 

[Kaylen]

Thank you so much GL!!! But one more problem....I need to have the result in
percent instead of decimal. But the field property won't allow me to choose
any format. I think it reads it as a text and return a decimal answer. How
can I format the sult of the formula to % instead of decimal?

I tried the formula, it returns a 0% when all the data is "N/A" but it was
incorrect when the data is not "N/A". 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......



- Show quoted text -

Try this:

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

- GL

 

[John W. Vinson]

Thank you so much GL!!! But one more problem....I need to have the result in
percent instead of decimal. But the field property won't allow me to choose
any format. I think it reads it as a text and return a decimal answer. How
can I format the sult of the formula to % instead of decimal?

Display it on a Form or Report and set the format of the textbox. Queries
aren't designed for data display but for selection and calculation.

 

[Kaylen]

I tried putting the result in forms and report but still there is no option
in properties where I can change the result to percent format. When I used
the original formula in the query which returns error message when the data
are all N/A, I was able to select the percent format in the query and in form
but with this formula I cannot change the format.

 

[John W. Vinson]

I tried putting the result in forms and report but still there is no option
in properties where I can change the result to percent format. When I used
the original formula in the query which returns error message when the data
are all N/A, I was able to select the percent format in the query and in form
but with this formula I cannot change the format.

ok... sorry! I see now it's already being cast as a string, and Percent format
applies only to number fields. You'll need to embed a call to the Format
function within the expression. Try

=iif(
(DCount("[UM]![13]","UM","[UM]![13]='Y'")+
DCount("[UM]![13]","UM","[UM]![13]='N'"))=0,
"N/A",
Format(DCount("[UM]![13]","UM","[UM]![13]='Y'")/(DCount("[UM]![13]","UM","[UM]![13]='Y'")
+DCount(" [UM]![13]","UM","[UM]![13]='N'"))), "Percent")

 

[Kaylen]

Thank u sooo much John. I hope I dont suond too greedy or bothersome, can u
help me with this? any suggestion is VERY much appreciated.

I am hoping that 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.