I used Allen Browne's method (http://allenbrowne.com/ser-67.html) to populate 2 values in a crosstab query. The first value is a number of dials, the second value is the duration of the calls in seconds. This is working as expected.
I am wondering though if it is possible to use the crosstab query as the source to update the format of the duration from seconds to hh:mm:ss or to format just the duration portion of the results during execution. When the initial crosstab is run it generates the values as Date + Dials and Date + TT
Currently I use the following to obtain the 2 values for the crosstab, with the values of Dials and TT as rows in the tblXTabColumns table per Allen Browne.
When the final crosstab is run it creates a dials column and a TT column for each day of the month as the data flows into the base tables. So day 1 it returns 2 columns and on the 30th day it returns 60 columns. It is here where I am running into the issue of formatting. I have not been able to get it formatted during execution and I am unsure if I can get it formatted using the crosstab as a query source.Code:TheValue: Sum(IIf([FldName]="DIALS",CDbl([DIALS].[DIALS]),CDbl([TALKTIME].[CUST_TT])))
Any advice as always is appreciated