Report to count number of objects in a date range.

[jambonjamasb]

I am wanting to create a report that summarises the number of items within a date range.

For example I have a FIELD called System_Change.

This is a drop down COMBOBOX that uses words like unix, polfs etc.
I know how to query a date range, but not how to count number of fields within this date range.

I would like the report to show date range filtered then list all changes and number of times they appear.

Thanks in advance anything to point me in the right direction.

[ADezii]

I am wanting to create a report that summarises the number of items within a date range.

For example I have a FIELD called System_Change.

This is a drop down COMBOBOX that uses words like unix, polfs etc.
I know how to query a date range, but not how to count number of fields within this date range.

I would like the report to show date range filtered then list all changes and number of times they appear.

Thanks in advance anything to point me in the right direction.

I'll give you a simple demonstration to illustrate how this is done. Let's say on a Report I want the Total Count of all Employees (in tblEmployees) who were born in 1968 ([BirthDate] is the Field), and who currently reside in the State of ennsylvania ([State] = PA):

1. Create an 'Unbound' Text Box on your Form.
2. Set its Control Source equal to the following Expression:
   [CODE=text]
   = DCount("*", "tblEmploye es", "[BirthDate] Between #1/1/1968# And #12/31/1968# And [State] = 'PA'")[/CODE]
3. Is this what you are referring to?

[jambonjamasb]

I'll give you a simple demonstration to illustrate how this is done. Let's say on a Report I want the Total Count of all Employees (in tblEmployees) who were born in 1968 ([BirthDate] is the Field), and who currently reside in the State of ennsylvania ([State] = PA):

1. Create an 'Unbound' Text Box on your Form.
2. Set its Control Source equal to the following Expression:
   [CODE=text]
   = DCount("*", "tblEmploye es", "[BirthDate] Between #1/1/1968# And #12/31/1968# And [State] = 'PA'")[/CODE]
3. Is this what you are referring to?

I dont need to put date as this is a dynamic parameter I set, does this mean where u put birthdate I would put "[System_Change] "POLFS""UNIX""N WB" or could i put a wildcard symbol? As these are the words that populate the field that i wish to count.

[ADezii]

I dont need to put date as this is a dynamic parameter I set, does this mean where u put birthdate I would put "[System_Change] "POLFS""UNIX""N WB" or could i put a wildcard symbol? As these are the words that populate the field that i wish to count.

I'm not sure if I am reading you correctly, but if you wish to Count the number of times 'either one' of those three values appear in the [System_Change] Field, then:
[CODE=VB]
=DCount("*", "<Your Table Name>", "[System_Change] IN ('POLFS', 'UNIX', 'NWB')")[/CODE]
If I am mistaken in my assumption, I apologize, please clarify.

[jambonjamasb]

I'm not sure if I am reading you correctly, but if you wish to Count the number of times 'either one' of those three values appear in the [System_Change] Field, then:
[CODE=VB]
=DCount("*", "<Your Table Name>", "[System_Change] IN ('POLFS', 'UNIX', 'NWB')")[/CODE]
If I am mistaken in my assumption, I apologize, please clarify.

Thanks ADezii, That should work its just I have 30 diff words to count so Iwill have to write a massive bitof code, I was hoping you would suggest setting it as a string to count. Thanks though ADezii I will give this a try.

Tony

[ADezii]

There is another approach that youo can take on this:

1. Create a Table, or use an existing Table/Query, that contains all the values for which you want to find a match for, namely POLFS, UNIX, NWB, etc.
2. Set the Control Source of a Text Box equal to a Function.
3. This Function will check the values in the [System_Change] Field in every Record against the Table of matching values in your Values Table.
4. If a match exists for a given Record, increment a Counter, move on to the next record, and repeat the pattern up to the last Record.
5. The Function then returns the Total Number of Matches to the Text Box.
6. To add/remove values to match on, simply Delete or Add them from the Values Table.
7. If you are interested in this approach, let me know.