InfoPath Dev

I have created a DB in MSSQL and a couple of forms in Infopath 2003

The problem form retrieves the info from the MSSQL database and puts it in a repeating table and i can run queries to show only the relevant data but the main purpose of the form is to get who took the longest or shortest time per task or day or Activity.

eg..

Date           | Name     | TIME Taken | Activity

1-Jan-2003  | Jim         | 00:15          | Invoicing

4-Jan-2003  | Tony       | 00:35          | Labelling

7-Jan-2003  | Jim         | 01:00          | Invoicing

                                   01:40  - - - Total Time Taken

The problem is when i try to calculate the Sum of the Time taken it gives a (1.#QNAN) error.

I have tried the Infopath Blog   http://blogs.msdn.com/infopath/archive/2007/02/21/calculating-elapsed-time-without-code.aspx

But that didnt work just wondering if im doing something wrong or if the fact that MSSQL store additional irrelevant info such as the date field i enter gets a time assigned (00:00) and the time field gets a Date assigned even thoug i dont use those parts could they be affecting the calculation.

 Hi

Welcome to the forums.

First thing, 15 minutes plus 35 minutes is 50 minutes plus one hour is 1h 50 but it doesn't matter either way I know what your problem is.

It is the fact that the fact that InfoPath adds only  cannot natively convert hours and minutes into just minutes to calculate the time.  The Blog details how to get hours into minutes and then calculate the difference, the only difference is you will want to convert dates as well so each day * 24 then * 60 to give minutes then take one from the other as the blod details and it will work fine.

What you need to make sure is that your Total Time Taken field is also not in the same table as the Time Taken.  It needs to be in a table 1 if the Time Taken is in table 2 with tables one and two on a one-to-many relationship respectively like this:

Here the total time field belongs in group1 but the rest of hte fields belong in group2 a subsisdiary of group1.  That way the sum function will work.

Cheers