-
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
-
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
-
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
Code:
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:
Code:
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
-
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
