DSum - How do I sum data from a date range report 1

[bethabernathy]

Hi There:

I am trying to calculate the following in a report:

The total number of incidents listed in the report that is based on a date range x the total number of incidents in the database.

I am trying to use the DSum function but can't seem to figure out how the syntax should work?

Thanks for any possible help. Beth

 

[CosmoKramer]

Beth,

How about the DCount function. Run it once with your date range as the criteria, and again with no date range (or no criteria) to select all incidents.

HTH

 

[bethabernathy]

HTH,

I think I explained my problem incorrectly. So this is it:

1) My report needs to generate a percentage of total the total number of incidents in the database by the total number of incidents listed in the report.

2) The incidents listed in the report are based on criteria in a query that prompt you for a date range (i.e. Between [Enter Start Date] And [Enter End Date]).

So I think the calculation would be the data generated in the report divided by the total records in the database x 100.

I can set the properties of the text field to display a percentage, but don't know the syntax to generate the results???

Any ideas.... and by the way, thank you for your AWESOME help!!!

Beth

 

[CosmoKramer]

Beth,

Try defining something like this for your percentage text field's control source:

=(DCount([Incidents],"tblIncidents","..put your date criteria here...") / DCount([Incidents],"tblIncidents"))

The first DCount will count records that meet your date criteria, the second one will count all records. If you have the text box already formatted as Percent, you don't have to multiply by 100.

HTH

 

[JoeMiller]

You won't be able to get this to work if you're trying to do the DCount using the query with the [Enter Start Date] and [Enter End Date] as your lookup source. You'll have to go to the table directly as Cosmo said. Couple different ways to do it if you must use the query as your source, the easiest of which is having a form where the user enters their dates, and the query uses the form to get it's criteria rather than the parameter value popups. Joe Miller
joe.miller@flotech.net

 

[bethabernathy]

Hi HTH, This is quite the project. So, the idea is to allow the user to be able to choose and enter the date range via pop up box that is generated by the

Between [Enter Start Date] And [Enter End Date]

that is in the base query, will the above work? This is what I have tried, but I think I am on the wrong track:

=DCount("[YellowcardID]","tblyellowcard","Between [Enter Start Date] And [Enter End Date]&quot/DCount([YellowcardID],"tblyellowcard&quot


Any other ideas???? Thanks so much

Beth

 

[tempclerk]

If your dates are being prompted for by the query, you may be better off creating the sum in the report itself and putting that field name in place of the first DCount in CosmoKramer's expression. Also, if the incidents aren't listed individually, use DSum. It works the same, just adds values instead of counting records.

 

[tempclerk]

=DCount("[YellowcardID]","tblyellowcard","Between [Enter Start Date]
And [Enter End Date]&quot/DCount([YellowcardID],"tblyellowcard&quot

First of all, you need to include What is "Between [Enter Start Date] And [Enter End Date]", but Joe's point is that this will prompt you to enter the start and end dates again, leaving you open to typing errors, etc. If instead you put the dates on a form, reference the form fields in the query, and reference the form fields in your DCount, you only have to enter them once.

 

[bethabernathy]

UUUGGG!!! There is a real love hate thing going on with this project of mine.

I removed the date range selection criteria from the query and add this syntax to the text box on the report:

=DCount("[YellowcardID]","tblyellowcard","Between [Enter Start Date] And [Enter End Date]&quot/DCount([YellowcardID],"tblyellowcard&quot

Well, then I get an error. So, I am going to try the form route, but haven't ever really done that so... anyway...

Is this what I do:

1) When the user goes to run the report a form pops up where the user enters the date range for their criteria?

2) Remove the date range criteria from the query that the form is based on?

3) Create something that will then run the report?

4) Then add the syntax in the report?

I am sort of lost here. Can you tell????????????????????

 

[CosmoKramer]

Beth,

You said earlier "the total number of incidents listed in the report.. If the detail lines are records you want to count, couldn't you try this:

=Count([YellowcardID]/DCount([YellowcardID],"tblyellowcard")

This will count your detail records and divide by all records in your table.......

 

[bethabernathy]

YAY!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Thanks HTH - It worked perfectly.

Beth