Beginner's help using count/Dcount function

**Salad** · Nov 12 '05, 10:44 PM

Re: Beginner's help using count/Dcount function

Simon Matthews wrote:
[color=blue]
> Hope someone can help an Access beginner!
>
> I've just started keeping my surgical logbook on access and it's a
> simple flat-file affair. I have created several queries that will
> list cases performed at different hospitals and reports based on the
> queries to print out the relavent details.
>
> What I would like to do is have a summary sheet in the Report Footer
> section that lists a grid of each type of procedure performed as well
> as the degree of involvement (1 to 4, depending on how much assistance
> was required). I have tried using unbound text boxes and playing
> around with the ControlSource doohickey to no avail. I thought it
> would be something like:
>
> =count([procedure]="Hip Arthroplasty" AND [code]="1"), repeated for
> all of the different boxes with different values of procedure and
> code....
>
> ...but I can't figure the exact syntax - dcount seems even more
> difficult. I've tried my access book and various ng's/knowledge base
> with no success.
>
> I am sure there must be a simple answer but I'm going around in
> circles - any help would be gratefully received!
> Thanks in advance.
> Simon Matthews[/color]

Dcount() is OK to get counts. Just remember if records don't exit,
DCount() returns null. So you might want to enter
NZ(Dcount("Fiel dNameToCount"," Table","YourFil terClause"),0)

But it the grand scheme of things you need to hardcode the whole thing
and then the report turns into a PITA everytime something new comes
about, and then you spend time programming an Access report instead of
operating and filling out medical forms and your nurse quits because you
don't work on patients but computerprogram s so...

I would suggest you create another report that lists the procedures and
counts. You can do this with a totals query as the report's recordsource.

I'm not sure of your skill level or how you are calling the report. I
usually call reports from a form. The form usually contains fields I
will be filtering on. Then in the OnOpen event of the report I might
set my filter. Ex:
Sub Report_OnOpen
Me.Filter = "Between #" & Forms!CallingFo rm!FromDate & "# And #" &
Forms!CallingFo rm!ToDate & "# And Code >=1 And Code <= 4"
Me.FilterOn = True

Or in the calling form, I might add a field and set it invisible. When
I press the button to call/open the report, I might create the filter. Ex:
Sub CommandPrint_Cl ick
Me.FilterValue = "Between #" & Me!FromDate & "# And #" & Me.ToDate &
"# And Code >=1 And Code <= 4"
Docmd.OpenRepor t "YourReport "
End Sub
and in the OnOpen event enter
Me.Filter = Forms!CallingFo rmName!FilterVa lue
Me.FilterOn = True

OK, you can now call the filter for this sub report that lists your
summary. Now open the main report. Click on the sub-report control in
the toolbox and drop that in the ReportFooter band. Make sure the
ReportFooter band can grow (dbl-click on the footer band, view the
properties). BTW, there is no master/child link in your case.

What I usually do on a case like yours is to create the main report on a
query. I then add another column Ex:
Master : "M"
Now I open up the main report and from the menu press View/Sort&Group.
I create a group called Master and I slide it up to the top....it
becomes the first group. Then I tell Access that Master would like a
footer. And then I drop the subreport into that band, not the report
footer. Report and page footers print at the bottom of a page. I like
to keep my extra data near the area where the last record printed.

Good luck.

Beginner's help using count/Dcount function

Beginner's help using count/Dcount function

Comment