Problems with DCount Function in Report

[SnoWBunnY]

I am working on and incident report database.
I have created a report for viewing information on the People Affected but before viewing this report, from a form, you must select who is affected from the option group .... "1" = Employee, "2" = Patient, "3" = Visitor / Volunteer, the value goes into the [PersonAffected] field. and then you select the beginning date and the ending date.

Now, on my report I have a summary box with the following information:

2002 Summary:

Quarter: #Incidents #EmplInci #PtInci #V/VInci
1 2 0 0 2
2 2 0 1 1
3 15 4 8 3
4 1 0 1 0
----- ----- ----- -----
Totals: 20 4 10 6

well actually that is the information that I am supposed to have but I am doing something wrong in my DCount function and the information appears like this:

Quarter: #Incidents #EmplInci #PtInci #V/VInci
1 2 4 10 6
2 2 4 10 6
3 15 4 10 6
4 1 4 10 6
----- ----- ----- -----
Totals: 20 4 10 6

this is an example of my DCound function:
=DCount("[PersonAffected]", "IncidentsByPersonQuery", "[PersonAffected] = '1'&quot

please help if you know why it is not counting the amount of incidents per person affected = '1' (meaning person affected being employees) FOR EVERY quarter.

Thank you!
=o)

 

[CosmoKramer]

That DCount example you posted should return all records where [PersonAffected] = '1'. Did you try adding the Quarter as criteria also??

 

[SnoWBunnY]

Hello!

I had tried a few lines of code involving quarter but without having any clue as to what I was really entering. Either way they never worked, I tried to search for information on dates and qtr but I really don't know what exactly I need to know on quarters so my searches have not been successful.

 

[CosmoKramer]

It think it would be something like this:
[/code]
=DCount("[PersonAffected]", "IncidentsByPersonQuery", "[PersonAffected] = '1' AND [Quarter] = 1&quot[/code]

 

[SnoWBunnY]

I tried the code that you gave me and I will show you the data that I get in a bit. I think that I have found a solution, let me know what you think please.

when I use

1) original one:
=DCount("[PersonAffected]", "IncidentsByPersonQuery", "[PersonAffected] = '2'&quot

this is what I get
* supposed to be data
** data that I get from testing
Quarter: *#PtIncidents **#PtIncidents
1 0 10
2 1 10
3 8 10
4 1 10
------- ------
Totals: 10 10


2) original + DatePart:
=DCount("[PersonAffected]", "IncidentsByPersonQuery", "[PersonAffected] = '1' AND DatePart(['q',[DateIncident]) = 1&quot
//DatePart can be = 1, 2, 3 or 4
Quarter: *#PtIncidents **DatePart=2 **DatePart=3
1 0 1 8
2 1 1 8
3 8 1 8
4 1 1 8
------- ----- -----
Totals: 10 10 10

so when I use DatePart=3, it knows that the amount of incidents for the 3rd quarter is 8 but it writes it out for all of the quarters.


3) original + [Quarter]
=DCount("[PersonAffected]", "IncidentsByPersonQuery", "[PersonAffected] = '1' AND [Quarter] = 1&quot //quarter can = 1, 2, 3 or 4
Quarter: *#PtIncidents **[Quarter]=1 **Quarter=3
1 0 10 0
2 1 0 0
3 8 0 10
4 1 0 0
------- ------ -----
Totals: 10 10 10

so this one knows which quarter is which
therefore I think that if I use them all together like this

4) original + DatePart + [Quarter]
=DCount("[PersonAffected]", "IncidentsByPersonQuery", "[PersonAffected] = '1' AND DatePart('q',[DateIncident]) = 1 AND [Quarter] = 1&quot

I will get what the information that I need but I will need to code in vb instead of calculating the control because I will have to use a loop because DatePart=1 and [Quarter]=1 go together but then I will need DatePart=2 and [Quarter]=2 then DatePart=3 and [Quarter]=3 and the same for 4.
What do you think?
have I lost you yet?

 

[CosmoKramer]

Are each of these fields distinct text boxes on your report? If so, couldn't you code the control source to be the criteria you need for each box, manually incrementing the values that you need to?

=DCount("[PersonAffected]", "IncidentsByPersonQuery", "[PersonAffected] = '1' AND DatePart(['q',[DateIncident]) = 1")

then:

=DCount("[PersonAffected]", "IncidentsByPersonQuery", "[PersonAffected] = '1' AND DatePart(['q',[DateIncident]) = 2")

etc.....

 

[SnoWBunnY]

Hello!

no they are not distinct text boxes
I only have 5 text boxes
* Quarter
Control Source: = Format([DateIncident], "q&quot
* IncidentID:
Control Cource: =Count([IncidentID])
* PersonAffected1
* PersonAffected2
* PersonAffected3
Contol Source: =DCount ("[PersonAffected]", "IncidentsByPersonQuery", "[PersonAffected] = '1' AND DatePart(['q',[DateIncident]) = 1&quot

all of this information (summary) is in the subreport which is sorted and grouped by DateIncident: by year and by quarter, the main report is sorted and grouped by DateIncident by year by each value and the IncidentID by each value.

so lets say that I have data for 1st quarter, 2nd quarter and 3rd quarter but none for the 4th quarter then my results will only be
Quarter:
1
2
3

it won't show the 4th quarter at all
hmmmm so ya

by the way thank you so very much for your time and patience it it greatly appreciated
if you have any more solutions answers suggestions or what not please feel free cause I am not sure as too how close I am to getting this one