CrossTab Query as a source for a query to update Format

[mindbender]

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.

TheValue: Sum(IIf([FldName]="DIALS",CDbl([DIALS].[DIALS]),CDbl([TALKTIME].[CUST_TT])))

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.

Any advice as always is appreciated

[June7]

What do you mean by 'update format'? You want to modify value in table? Why? Did you try using the CROSSTAB in another query and apply formatting?

[mindbender]

I have been trying to use the Crosstab in another query to update formatting but I seem to always get Data Type Mismatch for every row.

I was trying

Update tblExport_Temp
Set [TheValue] = Format([TheValue]/86400, "hh:nn:ss")
Where Expr2 like '*TT'

[June7]

Again, why would you update a table? Export query.

Format() returns a string value. If you are trying to update a number or Date type field, that would cause Mismatch.

[mindbender]

I thought I had to save the data to a table in order to get the seconds converted to hh:mm:ss, How would you recommend I perform that step? I appreciate you time and apologize for my thickness, new to this.

[aytee111]

Create a query to reformat the field, save it as a standard query that you can use everywhere in your system whenever you need that format. For instance, your crosstab query would then bring in this query and join it to the table on primary key, and will take the field from there already formatted instead of worrying about having to do it in the crosstab.

[June7]

Issue originally discussed in http://stackoverflow.com/questions/4...54057#43854057

@aytee111 How would the CROSSTAB join with standard query? CROSSTAB is an aggregation of data. Primary keys are not in CROSSTAB. Also, the standard or GROUP BY query has data vertical whereas in the CROSSTAB it is horizontal, how would you associate the values?

@Mindbender If you used the query builder to construct the UPDATE query should be able to do same for a SELECT using the same Format() expression.

[mindbender]

Issue original discussed in http://stackoverflow.com/questions/4...54057#43854057

@aytee111 How would the CROSSTAB join with standard query? CROSSTAB is an aggregation of data. Primary keys are not in CROSSTAB. Also, the standard or GROUP BY query has data vertical whereas in the CROSSTAB it is horizontal, how would you associate the values?

@Mindbender If you used the query builder to construct the UPDATE query should be able to do same for a SELECT using the same Format() expression.

I will try that thanks

[mindbender]

I got it formatted the way I wanted it now. Thank you very much for the help.