Access DCount problem 1

[rugbyguy]

Hope someone can help,

I am using DCount in a report to count all live births (all communties). Using the script below it does this job well. My problem is when I sort my report by community, it still returns all births for all cummunities.

My report is based on a query, with a community and date criteria.

=DCount("[Birth_Type]","[Birth]","[Birth_Type] = 'live'")

Any ideas....

 

[BobJacksonNCI]

Sorting the data does not change the Dcount instruction, so what you're getting makes sense.

If you'll post what you want to see, along with the SQL for your query, there's a very good chance someone here can help.


HTH,
Bob

 

[wemeier]

If "when I sort my report by community" really means "when I group my report by community", then you can't use DCount as you have it.

If the number of live births is on your report, try using the Count function instead. Count([livebirths]) in a Group SubFooter will count the number of live births in the subgroup (assuming you are grouping by community, that would mean the community).

Putting the Count function again in the Report Footer will return a count of all communities that appeared on the report.

Cruising the Information Superhighway
_{(your mileage may vary)}

 

[willir]

You can expand on the where clause component of your DCount function.

=DCount("[Birth_Type]","[Birth]","[Birth_Type] = 'live'")

If community is numeric
"[Birth_Type] = 'live' and Community = " & Me.Community

If community is a text / string variable.
"[Birth_Type] = 'live' and Community = " & "'" & Me.Community & "'"

Richard

 

[rugbyguy]

Thanks for the replies - I've used the count function and it works well.

The problem is as follows - my field [birth_type] has 2 variables in it -"live" and "Still/Mis".

When I put "live" in the criteria of my query - it returns all the lives births. Problem is that I also need all the "Still/Mis" returned from my query.

Perhaps this is best accomplished using the control in my report control source.

The crux of it is this:

I need my report to include a count of all "live" births in one text box. I also need the same report to include a count of all "still/mis" in another text box. They are both from the same field.

Thanks

 

[RoyVidar]

This is on a report?

Why not try the sum function in the report, and have the query select all? Two text controls on the report with the following controlsources:

[tt]=abs(sum([Birth_Type]="live"))
=abs(sum([Birth_Type]="Still/Mis"))[/tt]

- on my version, I didn't need single quotes, but you might try that too, if this doesn't work.

Roy-Vidar