473,324 Members | 2,257 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Report to count number of objects in a date range.

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.
Mar 24 '08 #1
5 5355
ADezii
8,834 Expert 8TB
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:
    Expand|Select|Wrap|Line Numbers
    1. = DCount("*", "tblEmployees", "[BirthDate] Between #1/1/1968# And #12/31/1968# And [State] = 'PA'")
  3. Is this what you are referring to?
Mar 25 '08 #2
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:
    Expand|Select|Wrap|Line Numbers
    1. = DCount("*", "tblEmployees", "[BirthDate] Between #1/1/1968# And #12/31/1968# And [State] = 'PA'")
  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""NWB" or could i put a wildcard symbol? As these are the words that populate the field that i wish to count.
Mar 25 '08 #3
ADezii
8,834 Expert 8TB
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""NWB" 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:
Expand|Select|Wrap|Line Numbers
  1. =DCount("*", "<Your Table Name>", "[System_Change] IN ('POLFS', 'UNIX', 'NWB')")
If I am mistaken in my assumption, I apologize, please clarify.
Mar 25 '08 #4
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:
Expand|Select|Wrap|Line Numbers
  1. =DCount("*", "<Your Table Name>", "[System_Change] IN ('POLFS', 'UNIX', 'NWB')")
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
Mar 26 '08 #5
ADezii
8,834 Expert 8TB
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.
Mar 26 '08 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: Massy | last post by:
Hi I have created a date-range parameter in crystal and i wanted to display the value of what the user has chosen in the title of the report I have dragged the parameter onto my report but when...
6
by: BlackFireNova | last post by:
Using Access 2002 I am writing a report which draws data from several different tables. I can't link all the tables in a query, as some can not be related without truncating the data. I plan...
4
by: Mark | last post by:
Hi I have been trying to convert the week number to a range of dates that I can use. It should be fairly simple for you guru's out there but for us mere mortals it is beyond our grasp. I know...
1
by: Doug | last post by:
I am sending out snapshot reports and I would like to change the name given to that particular snapshot report to include a date range instead of just the report name. Is there a way to add...
2
by: Sara | last post by:
I have followed instructions on the http://allenbrowne.com/tips.html for limiting a report to a date range. At the bottom there is a note that says You will end up using this form for all sorts...
19
by: ali3n8 | last post by:
Hello I have attempted to create a date range report from a query called qrycustomerinformation. The field that contains the value of my date is called Followup. When i run a report on this it is...
1
by: Mrbanner | last post by:
I have a new database file which i have created in my Table i have a field called name where 5 names can be selected from list also have a another field where the date is typed. What i need to do...
3
by: 6afraidbecause789 | last post by:
If able, can someone please help make a Where clause that strings together IDs in a multi-select listbox AND includes a date range. I wasn’t thinking when I used the code below that strings...
12
smithj14
by: smithj14 | last post by:
I have a form to enter start and end dates then select a worker name to filter a report. This all works fine and when the report is open in preview mode it shows the date range in the txtboxes on the...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.