Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Aug 2001
    Location
    Cleveland, Ohio, Ohio, USA
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Report text box reference (2000 sr1)

    Think my first post on this problem was lost some how.

    Have a report that has three groupings. Customer, Job, Date with Header and Footer sections. In the Date footer, I use Count(*) to find the EmpCount and LastDate - =Last(Date). LastDate is used so I will have only one date instead of one for each employee. In the Report footer I tried to use Count(Reports![Copy of All Job Data qry]![LastDate] and I get 0. Also I need to calculate Travel time for the Job - could be several days and for the Customer - several Jobs. In the Day footer, I use [EmpCount]*[Travel](from report query) to calculate the DayTravel. In the Job footer, I want to sum the DayTravel and I used Sum(Reports![Copy of All Job Data qry]![DayTravel]) and I get 0. I also need Total Travel for the Customer and Total Travel for the Report. But the formula stated above does not work. Would someone show me the error of my ways in this. I have also tried in the different footers =[DayTravel] with the RunningSum set to Over Group and Over All with no results. Currently I'm using about 80 queries to get the Report Totals in sub-reports for 4 Reports. Also there are many extra fields in other queries to get the Job and Customer totals.

    An aside, when I type in Reports![ReportName]![FieldName], Access will add [ ] to Report! - [Reports]! which I do not understand.
    Thanks much in Adavance,
    Jim

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Report text box reference (2000 sr1)

    As an aside, it's generally a bad idea to give fields the same name as built-in functions, objects, etc., like Date, for example. It *may* be allowed (or not), but it is never a good idea.

    It is not clear what your formulas are attempting to refer to. Is [Copy of all Job Data qry] the name of the report? If so, you don't reference it that way in a formula. Instead, you simply use Sum([DayTravel]).

    What do you mean you're using "about 80 queries to get the totals in subreports for 4 reports"? If you are, there is something seriously wrong with your data structures. Are you trying to summarize reports that are NOT subreports? You're going to have to clarify your question. There's no way to tell you why something doesn't work if we can't even tell what error you're getting and which report or subreport your formula applies to.
    Charlotte

  3. #3
    Star Lounger
    Join Date
    Aug 2001
    Location
    Cleveland, Ohio, Ohio, USA
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report text box reference (2000 sr1)

    Good Morning,
    Yes, I know that I should not use function names in the field names, I let that one slip by.

    Sorry that my post was confusing. I'm trying to sum the [DayTravel] in the Job footer, Customer footer, and in the Report footer. I tried to do this originally by using the =Sum([DayTravel]) formula in the three locations. I got a "Enter Parameter Value" box asking for DayTravel. This is why I went to the fomula notation =Sum(Reports![Report Name]![Text Box Name]) and that did not work either - no Parameter Value box but had a 0 for the value in Print Preview. [Copy of All Job Data qry] is the name of the report and the query for the data for the report.

    The other formulas I needed in the Report footer is to count the number of trips(DayTravel) to Job sites. And I need the number of trips that have a value >0. I have tried to Count the DayTravel, Date, and other text boxes with no luck. We are trying to get an idea of how many trips were made where there was no data.

    Because of the problem I have referring to calculated text boxes, in the Report footer, I'm using a subreport that has 8 fields that give me totals - total trips, trips over 0, labor hours, etc. To get the values, I needed to use about 20 queries to sort out the data doing calculations to count, sum, average, etc.

    I hope that this clarifies what I'm trying to do.
    Jim

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Report text box reference (2000 sr1)

    If you got that kind of error, I suspect that you have a field named DayTravel in the underlying recordset, but not necessarily in the actual report. Another possibility is that you have a control called DayTravel which can confuse formulas because they aren't sure whether you want to sum a field or a control. If you do have a control with the same name as the underlying field, try changing it to something like txtDayTravel and refer to that in your formulas. How well it works will also depend on what part of the report txtDayTravel actually occurs and what you're actually trying to do. How is your report being grouped?

    In general, it's a bad idea to have objects with the same name in the database. Call your report something else to avoid confusing Access. I know the wizards suggest the same object name as the data source, but the wizards as idiotic and are still writing Access 95 code!
    Charlotte

  5. #5
    Star Lounger
    Join Date
    Aug 2001
    Location
    Cleveland, Ohio, Ohio, USA
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report text box reference (2000 sr1)

    Good Evening Charlotte,
    DayTravel was a unique name on the report. But the problem, I think, is that it is a calculated value - =[EmpCount]*[Travel]. Then I tried to do a calculation on the calculation, =Sum([DayTravel]) in the Job footer. I've been back to the Books and they say that "you can't do that", that you have to use values from the underlining source for the report. I have said before that I know enough to get myself in trouble and that is what I did I think.

    I have gone back and changed a couple of queries and the way I'm doing things. It is now simpler and the report generates much faster. I will be able to get rid of about 60 queries that I used. An example of what I need would be that I need to know 1)number of trips to the job sites and 2)how many trips have time data(the guys do not always write down the travel time). To do this, in one field in the query, I use TravelData: Sum(IIf([All Travel-Job by Day qry]![TravelDay]>0.01,1,0)) with Totals set to Expression. And in the second field, TravelDay for the field name and Totals is set to Count. I can get this data with out any problem and I use it in a subreport. Must say that I found the Sum(IIf( ... ) in one of the posts on this site.

    You said in your last post "something seriously wrong with your data structures" which made me go back and look at how I was doing things. You were right, I've been able to simplify my queries and reports.

    Thank You for taking time with me again. I know that I will have many more questions for the group. There is much to learn!
    Jim

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Report text box reference (2000 sr1)

    Here's an alternate method:

    Abs(Sum([All Travel;Job by Day qry].[TravelDay]>0.01))

    That should be faster than using the IIF. What you're doing is evaluating *whether* the field is > 0.01 and summing the value of the trues and falses. Since True returns a -1, the Abs() function is used to convert it to a positive number.
    Charlotte

  7. #7
    Star Lounger
    Join Date
    Aug 2001
    Location
    Cleveland, Ohio, Ohio, USA
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report text box reference (2000 sr1)

    Thanks,
    Will give your formula a go. Am sure it will work better than mine.
    Jim

Posting Permissions

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