DCOUNT for Total 2

[lrdutches]

i need to get a simple total using the dcount and my mind has gone blank! i am pulling information by date and i need a total number count of how many responses that i had for a survey question for the date that i am pulling. this is what i tried but is not working!!
=DCount("[fieldname]","queryname","[fieldname]='fieldname'&quot

 

[PaulBricker]

Try this expression if you are looking for a string value.

=DCount("[fieldname]","queryname","[fieldname]= 'Tom Jones'&quot

Paul

 

[PaulBricker]

Sorry clicked to fast. If you are comparing the value to a field on a form then it would be
=DCount("[fieldname]","queryname","[fieldname]= '" & Me.FieldName & "'&quot

OK I'm finished.

Paul

 

[lrdutches]

thanks!!!!

 

[lrdutches]

ok - i have the dcount working for a field. now i need to take the dcount and divide this by the average that i have calculate for this field. how do i write this? my dcount answer is 4 and my average count is 2 so therefore the answer i am looking for is 2. i did the =(4/2) and i get the answer but when i pull the same information for a different date, the answers are not correct. don't i have to write this out in someway??? such as
=[fieldname]/[avg]

 

[PaulBricker]

You probably need to put the whole thing together in the Control Source for a textbox
=DCount("[fieldname]","queryname","[fieldname]= '" & Me.FieldName & "'&quot/YourAverageExpressionHere

You can't calculate a calculated textbox in a report. You have to use the entire expression again.

Paul

 

[lrdutches]

thanks, i will try this!

 

[PaulBricker]

If you have problems, post the expressions you are using and we'll try and work it out here.

Good luck.

Paul

 

[lrdutches]

well, paul, guess what - i am having trouble. i will get back tomorrow - too tired tonight. my e-mail is lrdutches@aol.com if you would rather help me figure this out through e-mail, that's fine. can a file be attached in this forum?

 

[lrdutches]

hello paul, i hope you are still there. my dcount is working but my division is not! this is what i have in a textbox for the control source.
=DCount("[ertimebeforeseen]","emergencyroom query","ertimebeforeseen]&quot/Avg([ertimebeforeseen])

thanks!

 

[PaulBricker]

It certainly looks like it should work. Where is this expression located? It should be in a group footer or the Report Footer.


Paul

 

[lrdutches]

my expression is in the report footer. i am so confused and new to all this. i have a textbox for the dcount in the detail section of my report (which is not visible) and this does work. i added 2 more records and this number changed from 4 to 6.

i was told yesterday that i could just use the expression
=(4/2) and get the answer which i did and it worked BUT when i added 2 new records my 4 doesn't change.

the four is the number of surveys that have been turned in. the two is the average for the answers that were marked for one particular question.

i'm lost!!!

 

[dhookom]

If the domain portion of your DCount() is the same as the record source of the report then you are creating more work and possible inaccurracies. The generic way to count the occurrences of a specific field value in a group or report footer is to use:
=Abs(Sum([FieldName]="SomeValue&quot)
or if numeric:
=Abs(Sum([FieldName]=3))

I have created 100s of reports and the only time you need to (or should) use DCount() or DLookup() or DSum() is when you want the referenced records to be different from the records returned in the report.

Duane
MS Access MVP

 

[rosieb]

dhookom
I've just spent best part of a morning failing to make DCount work properly on a report. This is perfect. Well worth a star!!

Rosie