2 Access report questions

[jeffro]

The first question how do I get the date to read 4/1/00 to 6/30/00 under the report title? I have based this report off of a query and have in the criteria for date (Between #4/1/00# and # 6/30/00#).

The second question is I would like to sort the total time field to descending. Well I have grouped the report by machine number in ascending order. When I make the total time descend before the machine number the groups fall apart. Is there a way that I can descend total time with out losing the groups of machine numbers?

Thanks

aja

Problem 1:
Inside the report you can put the date variable at the top of the output. Right Clicking the variable box will bring up a menu that has (at least on mine) the properties on the bottom. Click the properties and you get about half a million different things including:

ControlSource and Name

Change the control source to:

=str(min([datefieldname])) & " - " & str(max([datefieldname]))

I think you have to change the name property also.

This will give you a title of the ACTUAL start and stop dates. Normally, they will probably be the between dates, but you might not have a record of "04/01/00" if it were a sunday, for example.


Problem 2:
You can redefine the sorting order of the report without using the originating query.

On the Report Design Toolbar, there is a icon for Sorting and Grouping. On my installation it is next to the ToolBox (the crossed picks). Play around with it for a while and you should be able to do the second question with no problem.


Good Luck
DerFarm

[jeffro]

The first answer worked great, but I'm still working on the second. I have another question. For the grand total for total time the field is coming up in hours and minutes. The problem is I'm using endtime - starttime = total time and then when it goes to total all the times at the end of the report the total time is incorrect. How can I get the total time in hours and minutes to come up correctly?

Thanks aja

Are start and end defined as numeric or dates?

if they are defined as dates, you could try setting up another variable end-start as hrs. That should total correctly.

You can use calculated fields schema from above to do that.


Could you give an example of what it should be for x records (x>1) and what your getting?


Good Luck
DerFarm

[jeffro]

end time and start time are defined as date/time. I do have another variable Expr1:[End_Time]-[Start_Time], is that what you ment?

The answer for the grand total for total time is coming up with 5:11, which is 5 hours and 11 minutes. There are like over 50 records in this query so I know the time is wrong.

aja

Ok, a quick check shows that

EndDate: 12/15/99 12:03:28 AM
StartDate: 12/15/99

(EndDate - StartDate) =0.00240740740991896

You were expecting something similar to 3:28

3:28 = 3.46 minutes
3.46/1440 = .00240..... where 1440 is the number of minutes in a day

Therefore .00240....is the time difference expressed in terms of days

To achieve hours:mins:secs

  Diff = End-Start  Hours = trunc(Diff) * 24
  Diff = Diff - trunc(Diff)
  Hours = Hours + trunc(Diff*24)
  Diff = (Diff) - trunc(Diff)
  Minutes = trunc(Diff*60)  
  Diff = (Diff*60) - trunc(Diff*60)
  Seconds = trunc(Diff*60)

In terms of the above example:

  Diff = 0.00240740740991896
  Hours = Hours + trunc(Diff*24) = 0
  Diff = Diff - trunc(Diff) = 0.00240740740991896
  Hours = Hours + trunc(Diff*24)= 0
  Diff = (Diff*24) - trunc(Diff*24) = 0.057777777838055
  Minutes = trunc(Diff*60)  = 3
  Diff = (Diff*60) - trunc(Diff*60)= 0.4666666702833
  Seconds = trunc(Diff*60) = 28

  ANS=str(Hours) & ":" str(Minutes) & ":" & str(Seconds)

Alternatively, you can (if you aren't a REAL HE-MAN CODER)
use DateDiff("h",Start,End) will give you the difference
expressed in HH.dd where dd is the fractional hours.

Good Luck
DerFarm

[jeffro]

Not to sound stupid or anything but where would I put all that code and where could I use the DateDiff?

aja

Believe it or not, you can put the datediff in a viariable
on the report, very similarly to the date variable for the
heading of the report.


copy a variable box, change the source property to


=datediff('h',[startdate],[enddate])


If you wanted to use code, one of the tabs on your database
is titled modules

creating a new module will allow you to write functions
which can then be used in your queries like variables. I
wrote a really nifty one to perform Fisher's Exact for p
values when you have baseline and remeasurement numerators
and denominators. Works like a charm.


It's also a DANDY way to get into VB programming.

Good Luck
DerFarm