CONVERT NUMBER TO TIME FIELD - again

[brooksl]

My first post explained how I have to come up with a formula that converts a number to a field. An example of the data looks like this:

172,954 (is really military time for 00:17:29:54)
1,365,765 (is really military time for 01:36:57:65)
16,293,935 (is really military time for 16:29:39:35)

I need to convert my field, which is in number format to a time field (hh:mm:ss OR hh:mm) so that I can give my crystal report parameters and let the user define a date range based on two different fields that I need to convert as explained.

My formula (pasted below) tests as no errors found, but when I run the report, I get a numeric overflow message and it points back to the formula. Any help is greatly appreciated. I think maybe I have a parenthesis out of order or something simple, but haven't been able to find it thus far.

ctime(truncate(({CADLOG.CADLOG_ARRIVAL_TIME}/100000000)*100),
((truncate(({CADLOG.CADLOG_ARRIVAL_TIME}/1000000),2))-
(truncate({CADLOG.CADLOG_ARRIVAL_TIME}/1000000))*100),
((truncate(({CADLOG.CADLOG_ARRIVAL_TIME}/10000),2))-
(truncate({CADLOG.CADLOG_ARRIVAL_TIME}/10000)*100)))

 

[lbass]

You should have stayed with the original thread instead of starting a new one.

I would use a formula like this to convert:

stringvar x := totext({table.field},"00000000");
time(val(left(x,2)),val(mid(x,3,2)),round(val(right(x,4))/100,0))

-LB

 

[brooksl]

Thank you so much. I'll try that. Looks like it'll work and it's cleaner.

 

[brooksl]

That worked great. Thank you for the formula. I believe the report is just about working. I'm having a secondary problem, however, and not sure where to even start. I'm hoping someone has an idea. I have a report of Calls For Service from a dispatch center based on date range (arrival date) and time (arrival time) frame.

My problem is with the parameters, however. One of our shifts starts at 11:30PM (23:30:00 in my report) and ends at 7:30AM (07:30:00 in my report).

If I start my parameters with one day and end on the other, it doesn't recognize the time's listed above for this particular shift. I'm assuming it's because you can't go back in time from 11:30PM to 7:30AM.

I need to find a way to tell my report to go forward in time when it starts on one day and ends on another. As long as the user is only running one date and the time is going forward, everything works perfectly.

Any ideas are greatly appreciated!

 

[lbass]

What is your current record selection formula? How are your parameter(s) set up?

-LB

 

[brooksl]

The formula compiled in select expert based on my criteria is as follows:

{@Arrival Time FORMULA} = {?StartTime} and
{@NewDate} = {?Date} and
{CADUNITLOG.CADUNITLOG_DISPOSITION} in ["90", "35", "16", "01"] and
{CADUNITLOG.CADUNITLOG_ON_DEPT} = "01"

My parameters are set up for Start Date date range and a Start Time time range. My report filters out only certain records which are calls that generate some type of paperwork as opposed to no paperwork (i.e. police report filed vs. unfounded suspicious activity call).

 

[lbass]

Are shifts always the same? What are the start and end times for each shift?

-LB