I'm not sure I have the logic correct since I prefer "Less Than or Equal
to"
over "equal to or less than" so you may need to tweak this expression.
View
your SQL view of a query and copy the following:
SELECT COMPANY,
Sum(Abs( [Date BN S1 FWD to SRDC/HRC STL] <= [End Date] +7 ) ) as
TheCount
FROM [NCOER COMPLETE]
WHERE [End Date] Between
[Forms]![NCOERTimelinessForm]![NCOERStartDate] And
[Forms]![NCOERTimelinessForm]![NCOEREndDate]
GROUP BY COMPANY;
--
Duane Hookom
MS Access MVP
message My apologies, thank you for your continued assistance.
I would like to count the number of records by company where [Date BN
S1
FWD
to SRDC/HRC STL] is equal to or less than 7 days after [End Date], with
the
intent of finding out how many evaluation are sent to our higher
headquarters
on time (within 7 days of the end date).
:
You forgot to tell us "what you want to calculate based on those
fields".
I would expect you to tell us something like I want to count the
number
of
records by company where some field is so much greater or less than
some
other field.
--
Duane Hookom
MS Access MVP
message Here is the SQL view, although I don't understand it:
SELECT [NCOER COMPLETE].COMPANY, [NCOER COMPLETE].[End Date],
Count([NCOER
COMPLETE].[End Date]) AS [CountOfEnd Date], [NCOER COMPLETE].[Date
BN
S1
FWD
to SRDC/HRC STL]
FROM [NCOER COMPLETE]
GROUP BY [NCOER COMPLETE].COMPANY, [NCOER COMPLETE].[End Date],
[NCOER
COMPLETE].[Date BN S1 FWD to SRDC/HRC STL]
HAVING ((([NCOER COMPLETE].[End Date]) Between
[Forms]![NCOERTimelinessForm]![NCOERStartDate] And
[Forms]![NCOERTimelinessForm]![NCOEREndDate]));
:
Could you share you SQL view of your query? How about providing
some
field
names and what you want to calculate based on those fields.
--
Duane Hookom
MS Access MVP
in
message That worked great, thank you.
I now get a count of all the evals between the dates on the form.
What
I
would like to know next is of those evals, how many were turned
in
within
7
days of the thru date.
I tried using the following criteria without success in the field
Eval
Submitted (within the same query that counts the evals between
the
dates I
selected). Can you assist?
<[End Date}+8
:
Just create a form named "frmDateSelect" with two text boxes
"txtStartDate"
and "txtEndDate". You can then set the criteria under a date
field
to
something like:
Between Forms!frmDateSelect!txtStartDate and
Forms!fmrDateSelect!txtEndDate
--
Duane Hookom
MS Access MVP
"franklinbukoski" <
[email protected]>
wrote
in
message
Sir,
Would you mind explaining this step by step? Am I creating a
combo/tex
box
that is blank, not tied to a query, for a date format I
specify?
Then, how do I tie this field to a criteria in a query?
:
You should first create a form with a combo box or text box
to
select
the
date range/month. Use this as the criteria in a totals query
that
Counts
your results by company. Then use the Report wizard to create
your
chart.
You may need to change the Row Source of the chart after the
wizard
screws
it up.
--
Duane Hookom
MS Access MVP
"franklinbukoski" <
[email protected]>
wrote
in
message
Good evening,
I have a report that reflects the date an evaluation was
due
and
the
date
the company turned the evaluation in to my organization. I
would
like
to
create a report that reflects in a chart how many
evaluations
were
turned
in
on time, by company (I have 7 total), versus how many were
late,
by
month.
I would like this report to prompt me for the month I want
the
analysis
conducted on and then simply generate the chart
(pie/bar/line
whatever
works)
based off of the month I enter.
PLEASE HELP ME!
