James, there are several things going on here.
Internally Access stores date/time fields as a floating point number, where
the integer part represents the date and the fractional part represents the
time. This means that summing a date/time field (as you are doing with your
running sum) is going to give you problems. Firstly, if you suppress the
date part (e.g. by setting the Format of the control to Short Time), the
date values are still summed, and the control may overflow. Secondly, once
the sum reaches 24 hours, Access adds 1 to the integer part and displays the
remainder as the time of the next day. This means that summed values over 24
hours do not display correctly. They either show as far too few hours (if
formatted to suppress the date), or as a weird date in 1899 or 1900 (if
there is no date component), or a wildly future date (if there is a date
component).
As explained in the linked article, the best solution is therefore to use a
text box that sums the value in minutes (or seconds if you need that.) For
you case, the text box would have properties like this:
Control Source =DateDiff("n", #0:00:00#, [MyDate])
Running Sum Over Group
Format General Number
Visible No
Name txtMinutesRS
You can then display the value as hours an minutes in another text box with
Control Source of:
=[MinutesRS] \ 60 & Format([MinutesRS] Mod 60, "\:00")
Or, you could refer to the number of minutes in the hidden text box in the
Conditional Formatting, e.g.:
[txtMinutesRS] > 990
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
James said:
Thanks for the info but it does not do what I need.
The #4:30:00 PM# as the value to compare does not bold any of the records
when the field is beyond 4:30 PM. I tried selecting another field to
highlight with reference to the Jtime field using the expression is
argument.
I entered #4:30 PM# but none of the records were made bold even when the
Jtime field contained 5:15 PM. Anytime I used the #4:30 PM# as the value
for
Jtime, I get the #Name? error in my report for each record in the report
for
the Jtime field.
Your reference to using a numeric expression seems to deal with obtaining
the difference between different dates. That is not my problem. I have a
field with a time in it "4:30 PM" but the conditional formatting will not
bold those with a time > 4:30 PM as entered into the cond. format.
Allen Browne said:
Use the # delimiter around the time value in the the Conditional
Formatting,
i.e.:
#4:30:00#
A better solution would be to use a numeric expression for the time, as
explained here:
Calculating elapsed time
at:
http://allenbrowne.com/casu-13.html
I have a field that is a running sum of different times as the records
are
displayed. the format displayed is "4:30 PM" (I used format for field
in
report " h:nn ampm").
I have tried to use conditional formatting to make the time field BOLD
when
the time value is greater than "4:30 PM". I have tried using the
'greater
than' value with the value of ">="4:30 PM" but get #NAME error in
report.
I've used another text field with 'expression is' and used [Jtime] >=
"4:30
PM" it does not work correctly, I've also tried numeric values
representing
the time - nothing seems to work. Any suggestions? Thanks
