WHAT is the NAME of the DateField?
IIf(DCount("*","UM","[13] IN ('Y','N') AND [NameOfDateField] =
#9/9/09#")=0,
"N/A",FormatPercent((DCount("*","UM","[13] ='Y' and [NameOfDateField]=
#9/9/09#")/
DCount("*","UM","[13] IN ('Y','N') and [NameOfDateField]=#9/9/09#")),0))
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
I am soo glad that the answer is YES. But I would need more specific
help.
Here is the master formula that I use in the query field that filtered
out
a
specific date.
=IIf(DCount("*","UM","[13] IN
('Y','N')")=0,"N/A",FormatPercent((DCount("*","UM","[13]
='Y'")/DCount("*","UM","[13] IN ('Y','N')")),0))
How can I use this formula to direct Access to filter out the date for
example 9/9/09 in the TABLE "UM Files". [13] is a field with Y, N,
and
N/A.
Please help. I only have this one day to figure it out if I can. Thank
u....
:
Yes, you could add additional criteria to the where argument of the
expression. Since I have no idea of the date field name I can only
give
you
a generic idea.
DCount("*","UM","[13] ='Y' AND [SomeDateField] Between #2008-01-01#
and
#2008-01-31#")
Basically, you need to construct a WHERE statement without the word
WHERE
for the third argument of the DCount.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
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......
