-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hmmm...not too sure that using all those DCount()s is such a good idea.
It could slow down the query a lot 'cuz each occurrence scans the entire
Files table to get it's count. That's three table scans just for one
formula. You could make the expression an alias and use the alias in
other formulas. Example:
SELECT DCount("*", "TableA") AS theCount, theCount/600 As thePct, ...
Anyway, here's the change to your formula:
Question1: IIf(DCount("*","Files","[14] IN ('Y','N') AND [Review Date]
= #" & [Start of Quarter Date] & "# And [Review Date] < #" &
DateAdd("q", 1, [Start of Quarter Date]) & "#") = 0, "N/A",
FormatPercent((DCount("*","Files","[14] = 'Y' And [[Review Date] >= #" &
[Start of Quarter Date] & "# And [Review Date] < #" & DateAdd("q", 1,
[Start of Quarter Date]) & "#")/DCount("*", "Files", "[14] IN ('Y','N')
and [Review Date] >= #" & [Start of Quarter Date] & "# And [Review Date]
< #" & DateAdd("q", 1, [Start of Quarter Date]) & "#")),0))
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBSbgw2IechKqOuFEgEQJ+GgCeIIaY4hAURSgcD7Kpu8/+3C+T8qcAn29/
azu3tvmx16/3Ynt61cPn0NJz
=5xWy
-----END PGP SIGNATURE-----
Kaylen wrote:
You're right, it works now that i recopied and repasted it. Thank you very
much! There is another question I hope you can please help me on. This is the
main formula that I need help making it calculate for the quarter also. Can
you help me modify the formula below so that it calculate for the quarter? It
is similar to the one you have helped me on. I am very grateful for your help!
Question1: IIf(DCount("*","Files","[14] IN ('Y','N') AND [Review Date] =#" &
[Confirm Review Date] & "#")=0,"N/A",FormatPercent((DCount("*","Files","[14]
='Y' and [Review Date]=#" & [Confirm Review Date] &
"#")/DCount("*","Files","[14] IN ('Y','N') and [Review Date]=#" & [Confirm
Review Date] & "#")),0))
:
This formula worked on my computer. Please copy & post your formula and
the starting date you enter when the query runs.
--
MGFoster:::mgf00
Oakland, CA (USA)
Kaylen wrote:
I tried the formula but it gives me the count of all the files. I have sample
files from 1/1/09 through 5/1/09, and I expect the formula to count only
files from Jan to March, but it counts everything from the beginning date
until the last date I have. Can you help correct this formula so that it
counts only the begin quarter date to the 3rd month after? Or if there is a
formula where I can specify the begin date and the end date, that would be
fine too. Thank so much for your help!
:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Apparently, the # delimiters are needed in the DCount() function;
therefore, change to this:
Number of Files Reviewed: DCount("*","Files","[Review Date] >= #" &
[Start of Quarter Date] & "# And [Review Date] < #" & DateAdd("q", 1,
[Start of Quarter Date]) & "#")
Be sure to enter the first date of the quarter as the [Start of Quarter
Date].
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBSbdGw4echKqOuFEgEQL1CwCfT3a9uDCBHONSG4xcDaqTlKA3MwYAoLTn
PNaBh5libSmyhN5yVU5YST3Q
=Ib15
-----END PGP SIGNATURE-----
Kaylen wrote:
Thank you MG Foster for your input. I tried the formula and kept getting
zero. I have 18 files for the quarter but the formula resulted in 18 zeros
instead of just one number 18. My goal is to have the result for the quarter
in just one number, not for each single files. Can you help?
:
Kaylen wrote:
Good morning,
I have a formula which I need help on expanding to calculate for the
quarter. Currently this is my formula in a query I have:
Number of Files Reviewed: DCount("*","Files","[Review Date] and [Review
Date]=#" & [Confirm Review Date] & "#")
The user has to enter twice a single Review Date (twice because there are
two connected tables) to get the result of the count. Now I would like to
expand the formula to allow a count for the quarter, which means I would like
to be able to get a count of files for 3 selected review dates instead of
just 1. I would like the user to select or enter in three different review
dates so the formula can calculate the number of files reviewed for those
three dates using the formula above. Any help or suggesstion would be a very
much appreciated!
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
You only need one date to calc for the qtr. Something like this:
Number of Files Reviewed: DCount("*","Files","[Review Date] Between " &
[Start of Quarter Date] & " And " & DateAdd("q", 1, [Start of Quarter
Date])-1)
Set up the Parameters for [Start of Quarter Date] as a DateTime data
type 'cuz you won't have to use the # delimiter in the expression.
The user has to enter the exact 1st date of the beginning of the
quarter. E.g.: 1/1/09 or 4/1/09 or 7/1/09, etc.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBSbXtZIechKqOuFEgEQIXUQCg4/Ysd9++u9iJIxBIufLMmjKTDsIAoO5T
0mcf8mnQZDUaEUTwopKOR4Sw
=X1Wn
-----END PGP SIGNATURE-----
