Hi Michael,
>
WorkDate / TrainTime
>03/06/00
>03/07/00 4:00
>03/08/00
>03/09/00
>What is the syntax to select the "4:00" as the total training time for the days 3/6 - 3/9? (assumes only one value in list, else blank)
>
>Sele iif(!empty(traintime),traintime,space(4)) as TotTrain
>
>arbitrarily grabs the first record, evaluates the iif and likely stores the wrong value to TotTrain. This field is character, so sum() is not available on this field without a lot of string manipulation.
>
>Is there an easy way to do this?
I'm afraid, not. You should firstly select all records without grouping like:
select *, iif(empty(trantime),0,val(left(trantime,at(':',trantime)-1)) as TrainHours, iif(empty(trantime),0,val(substr(trantime,at(':',trantime)+1)) as
TrianMin. When do grouping and sum, then, probably, another select to format back into TrainTime.
If it's not broken, fix it until it is.
My Blog