Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 39
  1. #16
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,535
    Thanks
    0
    Thanked 23 Times in 23 Posts
    Glad you found it.

  2. #17
    New Lounger
    Join Date
    Apr 2014
    Posts
    22
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Um, actually I did not find it. When I transferred my new query and report Data to the company computer at work, the report shows the proper breakdown but shows it for ALL the calls in the Db which is about 22,000 calls

    I just cannot seem to get it to just show a breakdown of calls within a specified date range.

    Someone suggested directing the expression to either the Msys_obj table or the Query1 query, but that doesn't work. Also, someone suggested using: =Count(IIf([Time] =>#4:30# and [Time]=<#6:29#,0)) and I get an error message saying I may have entered a comma without a preceding value or identifier.
    Last edited by Kimmy; 2014-06-24 at 17:45.

  3. #18
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,535
    Thanks
    0
    Thanked 23 Times in 23 Posts
    Any chance of sending the relevant objects with test data in a zipped database.

  4. #19
    New Lounger
    Join Date
    Apr 2014
    Posts
    22
    Thanks
    1
    Thanked 0 Times in 0 Posts
    In the report's footer, the Grand Counts work fine, as so do the Outcomes. They both function in accordance with the user-selected date range. I don't know if it's something I have overlooked or something I have not yet learned about. The properties are set for integers. I'm in the process of learning about Access via instructional Cd's.
    Attached Files Attached Files

  5. #20
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,535
    Thanks
    0
    Thanked 23 Times in 23 Posts
    All your Dcount counts will also require the start and end date check.

    How you do this depends on you, if you use the query name it doesn't work due to a #Error problem, for these sorts of reports I normally have the start and end dates on a form and let the controls or queries reference them.

    I have plugged away on this and come up with the attached database.
    Attached Files Attached Files
    Last edited by patt; 2014-06-26 at 00:14.

  6. #21
    New Lounger
    Join Date
    Apr 2014
    Posts
    22
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I'm caught between a rock and a hard place. I'm at work where I have a good computer on High-Speed DSL, but I am not permitted to download anything or install a Zip File reader, so I can't view what you've put together. I have a copy of my Db on a memory stick and have been trying different solutions which I'll compare with your ideas when I get home tonight (your tomorrow) and get back to you. Thanks.

  7. #22
    New Lounger
    Join Date
    Apr 2014
    Posts
    22
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Okay. I just got home from work an hour ago and it's now 1AM here on Friday about 12 behind you. I'm fatigued but I'm looking at your modifications. A lot of new stuff here to learn, I see.

    Figured out your frm Detail and noticed that it correctly counts the number of calls within each time segment . But all the other calls in the other sections it displays a total of all the calls in the table -approx 5400- instead of by user-entered Date Range

    I see some new expressions in the reports that I will spend some time studying and experiment with redesigning the form so that I can get the form into the report so the user only has to enter Date Ranges once and the form automatically open with info as set by Date Range.

    As I said, a lot of new stuff here to work with and learn. BTW What id 1 NooF? That's a new one on me.

  8. #23
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,535
    Thanks
    0
    Thanked 23 Times in 23 Posts
    you wrote:
    Figured out your frm Detail and noticed that it correctly counts the number of calls within each time segment . But all the other calls in the other sections it displays a total of all the calls in the table -approx 5400- instead of by user-entered Date Range

    PT: I have not checked if all correct, you will have to check if the date range is being used to filter the records for that total. Anyway try this in the form that calls the report:
    Code:
        DoCmd.OpenReport stdocname, acViewPreview, , "[Date] Between #" & Format([Start Date], "mm/dd/yyyy") & "# and #" & Format([End Date], "mm/dd/yyyy") & "#"

    I see some new expressions in the reports that I will spend some time studying and experiment with redesigning the form so that I can get the form into the report so the user only has to enter Date Ranges once and the form automatically open with info as set by Date Range.

    PT: That's the beauty of having the date ranges entered onto a form, then all manner of objects can reference them.

    As I said, a lot of new stuff here to work with and learn. BTW What id 1 NooF? That's a new one on me.

    PT: Ignore that. in fact take it out of the query.

    If you require more help just ask.
    Last edited by patt; 2014-06-27 at 21:33.

  9. #24
    New Lounger
    Join Date
    Apr 2014
    Posts
    22
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I've been real busy at work and I'm sorry I haven't kept in touch with you. This is where I stand with the reports: The Tally report displays incorrect counts for the counties but your input for the phone calls breakdown divided into 4 segments works fine. On the other hand, the second report shows a correct count of the counties but the calls breakdown isn't displaying data by date range, its displaying a breakdown for all the data in the table.

    My knowledge of SQL and Visual Basic is almost non-existent so I am currently taking online courses which covers this as well as Access 2007 in hopes of gaining a better insight into resolving the problem. I'm still tinkering with your suggestions. Kimmy

  10. #25
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,535
    Thanks
    0
    Thanked 23 Times in 23 Posts
    Have you changed anything in the database I sent, if so, would you send your database again

  11. #26
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,535
    Thanks
    0
    Thanked 23 Times in 23 Posts
    The Tally report displays incorrect counts for the counties but your input for the phone calls breakdown divided into 4 segments works fine.
    PT: Is that report called Detail Report? if so, which totals do not work, please be specific. I may be wrong but I have checked and they all seem to add up.

    On the other hand, the second report shows a correct count of the counties but the calls breakdown isn't displaying data by date range, its displaying a breakdown for all the data in the table.
    PT: I have made no changes to the Monthly Tally report.

  12. #27
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,535
    Thanks
    0
    Thanked 23 Times in 23 Posts
    I have made a few changes. Here is the latest DB.
    Attached Files Attached Files

  13. #28
    New Lounger
    Join Date
    Apr 2014
    Posts
    22
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Using your revised form, the Call breakdown appears to be working correctly. However, the Summary for Locations now display counts for the entire MSys_obj table Also, as so does the Grand Count and Averages.

    It's just a hunch, but I think the problem is in the Query1 SQL. Since the query now shows the combined time ranges from the entire table, I suspect I need to write the date range into it or somehow direct it to the form results. I'm experimenting with that now.

  14. #29
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,535
    Thanks
    0
    Thanked 23 Times in 23 Posts
    I have been testing with a date range of 1/1/14 to 1/1/14, and the totals seem ok to me. Which totals in particular are wrong?

  15. #30
    New Lounger
    Join Date
    Apr 2014
    Posts
    22
    Thanks
    1
    Thanked 0 Times in 0 Posts
    For 1/1/2014 this is what I see: At the very top of the report, Broome County - 1,032 calls. That's way off. We usually receive maybe 5 calls at best.

    However, The Call Frequency Breakdown for that day seems right: 27 calls. I still believe the problem lays withing Query1. When I can, I'll run through it again.

Page 2 of 3 FirstFirst 123 LastLast

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •