Time Issue - Can't Total Times

[secelec]

Hi

CR9. I have fields "On" and "Off" which are time fields, and on each rowset a formula totals the time between the two - as per a timesheet, for example.

What I need to achieve is to total the times generated by my @Day Total formula for each weekly period.

I've tried all variations of the time commands in crystal syntax that I can think of, but seem to variously generate errors involving wrong data formats.

What do I need to do to get a total from this set of formulas?

Thanks for your help....

 

[synapsevampire]

Try posting technical data:

Crystal version
Database/connectivity
Example data (data types)
Expected output
Relevant formulas

You speak of a formula you created, and the layout by stating timesheet (there are lots of formats there) as if someone here knows what's contained therein. I'd start guessing, but prefer that you take the time to properly describe.

I would suggest that your formula should be generating minutes and then you can readily sum them and do math to show the difference.

Here's a FAQ on using seconds to display as HH:MM:SS which might help.

http://www.tek-tips.com/faqs.cfm?fid=3543

-k

 

[secelec]

Ah, OK. Sorry. Only just joined, and I wasn't sure how much info would be wanted......

I did put the version number in - 9. (Prof).
I'm connecting to an Access (2003) DB built specifically to take data out of an Access control program, and query the tables for the information needed. The query on which the report is built filters for the first time of the day and the last.

Specifically, the report is set up to group the date by week, then by department, then by user name. The detail is by user and is date logged (Date), card number(Number), first time(Time),last time(Time). On the end of this row, I have a formula field called {@Total Time}(Time).

I built that formula using Crystal Syntax, and it looks like this:

IIF ({TimelogRefined.ON}>{TimelogRefined.LastOfLOGTIME},TimeValue (00,00,00),TimeValue ({TimelogRefined.LastOfLOGTIME}-{TimelogRefined.ON}))

The aim of this is to ensure that the ON time is less than the off time - it returns silly amounts of time on site otherwise which are untrue. I've formatted these fields as Time, all the way through.

I haven't looked into using basic syntax at all using CR yet - fairly new to the application, but it seems to me that there ought to be a relatively simple way of having a field calculate the total value of the column containing {@Total Time} - as one might in say, Excel - =sum() - I appreciate that this isn't Excel, however.

I'll have a look at your FAQ now, and see if I can make sense of it.

Thanks for your quick reply and help.

Bill.

 

[synapsevampire]

Pretty much anything you do with Basic syntax you can do with Crystal syntax.

What you're probably looking for is the datadiff function here, but again, you didn't share the data types, you attempted to describe them instead.

I would suggest building an Access Query to handle the heavy lifting as opposed to doing so in Crystal.

Anyway, I assume that you must have a date field as well, so build a datetime type, and then use the datediff function to return the value in minutes or seconds and do the math as described in my FAQ.

Please post the format and data types of your time fields.

-k

 

[secelec]

Hi again - sorry for the delay in getting back to this one.

I'm trying to get to grips with precisely what you mean by your terminology of format and data types - but here's what I think you mean:

In my report, I have three groups on which the report is sorted:

By Department
By Date Period
By User

The Detail fields within the third group are:

Logdate - DateTime field formatted as Long Date
Card Number - Text Field
Time ON - this comes through from the DB as a text field.
Time OFF - this comes through from the DB as a text field.

Finally I have a formula field which currently contains the following formula:

DateDiff ("n",CDateTime ({Time_Records.TIME IN}),CDateTime ({Time_Records.TIME OUT}))

This gives a number field which is the number of minutes between Time ON and Time OFF.

My problems begin with summing this field - I'm getting unexpected results when trying to calculate on the formula field above - @Time On Site.

Wherever I put it, it seems to want to return the total number of minutes of ALL entries - it doesn't seem to calculate for the group period I'm looking at (One user in one department over a period of a week).

BTW - I have done some reworking of stuff based on what you said earlier - I also realised there were some errors in my DB queries which I've addressed - and hence the difference in some of the bits above. Essentially, I've taken the tables I need from the ACMS application which are in DBase V, imported them into Access 2003 and run three or four queries which eventually make a table with just the data needed in - i.e. Date of Transaction, Time of Transaction, whether ON or OFF, name.

Your help is appreciated - CR is a far more capable tool than I realised....

Bill.

 

[synapsevampire]

Place the formula in the details section.

Now you can right click it and select insert->summary-Sum for whatever group levels you want the sum for.

-k

 

[secelec]

Hi

Tried this and learned a lot new.

What I'm finding now is that it seems to be summing the number of records in the group, rather than calculating the total of the times inserted by the formula {@Time on site} which is now returning a value in seconds - e.g. if time on says 08:06:55 and Time off says 16:44:16 then I'm converting these using CDateTime in the following formula:

DateDiff ("s",CDateTime ({Time_Records.TIME IN}),CDateTime ({Time_Records.TIME OUT}))

What I need to do is to get the total of seconds, formatted as hours, minutes and seconds, for the group of records, rather than just the number of records.

I can't quite see where it's going awry, but I do appreciate very much the help you've offered so far - thanks.

Bill