Update statement using if

[mindbender]

I have used Allen Browne's method to create 2 value columns in a crosstab, dials and Talktime.
Expr1 field is either [the date] DIALS or [the date] TalkTime
TheValue field is either the number of dials or the amount of talktime in seconds

I am wondering if it is possible to format the talktime value to h:mm:ss using an If Statement in an Update query.

I have tried this but all it does is null out the talktime data in the [TheValue] field

UPDATE  EXPORT_TEMP
 set [TheValue] = IIf([EXPORT_TEMP]![Expr2] Like '*TT*',Format([TheValue]/86000,"hh:nn:ss"),[TheValue])

The table starts off looking like this




and after I run the update statement above it looks like this


Thanks as always

[JoeM]

I think the issue is it appears that your "TheValue" field is a numeric field, and the FORMAT function returns a Text value, not numeric.

Try this:

UPDATE  EXPORT_TEMP
Set [TheValue] = IIf([EXPORT_TEMP]![Expr2] Like '*TT*',[TheValue]/86000,[TheValue])

or better yet:

UPDATE  EXPORT_TEMP
SET [TheValue] = [TheValue]/86000 
WHERE [EXPORT_TEMP]![Expr2] Like '*TT*';

(it is better to use Criteria to only update the records that need updating)

You would then using Formatting Property on the field (and not the Format Function like you were doing) to format the look of the result.

[mindbender]

ok - so I used the second better yet option and I see the data updated as follows


I am not sure what you mean by Formatting Property on the Field to get the final results from the query to look like


Thank you for your help on this. This forum always comes through

[JoeM]

In your query, go to Design View, right click on the "TheValue" field from the Query Builder, select Properties, and enter "hh:nn:ss" in the Format property.

[mindbender]

ok duh moment.

So when I do that the TT data looks awesome but it also formats the Dials columns that way

Looks like this


When I would like it to look like this


I think it all goes back to having 2 values in 1 column using the magic of Allen Browne

[June7]

Okay, back to the Format() function.

Does Dials value have to remain a number type? Cast it to a string as well.

SET [TheValue] = IIf([EXPORT_TEMP]![Expr2] Like '*TT*', Format([TheValue]/86000,"hh:nn:ss"), CStr([TheValue]))

[mindbender]

that ends up doing the same thing as before where it nulls out the tt field. frustrating

[JoeM]

So when I do that the TT data looks awesome but it also formats the Dials columns that way

Why?
Did you apply the Format to that field as well?
Changing the Format property on one field should not alter the Format property of another unrelated field.

I think it all goes back to having 2 values in 1 column using the magic of Allen Browne

Not sure what you mean by "having 2 values in 1 column". Are you dealing with multivalued fields here? If so, I would strongly recommend against it. They can be problematic and difficult to use. I NEVER use them.

[mindbender]

Why?
Did you apply the Format to that field as well?
Changing the Format property on one field should not alter the Format property of another unrelated field.



Not sure what you mean by "having 2 values in 1 column". Are you dealing with multivalued fields here? If so, I would strongly recommend against it. They can be problematic and difficult to use. I NEVER use them.

This is the process I used

http://allenbrowne.com/ser-67.html

. Basically it allows you to have 2 values in 1 crosstab but they are in essence stored in the same column. This is where the issue lies. I cannot format the field like above because there is a number value and a time value in the same column. And when I run the update for format the time value to hh:nn:ss it nulls out the field

UPDATE  EXPORT_TEMP
 set [TheValue] = IIf([EXPORT_TEMP]![Expr2] Like '*TT*',Format([TheValue]/86000,"hh:nn:ss"),[TheValue])

[JoeM]

Basically it allows you to have 2 values in 1 crosstab but they are in essence stored in the same column.
...
I cannot format the field like above because there is a number value and a time value in the same column.

I don't think that returning two different types of data in the same column is a good idea. As matter as fact, it kind of violates rules of data normalization.

I think we would have to have a better idea of your data structure, and what exactly it is you are trying to accomplish (and why). There may be other ways of going about it.

Note that there is the ability to upload a copy of your database here. That often goes a long way in showing a lot of that information.
If you do decide to do that, please be sure to remove any sensitive data.
Also note that I, personally, cannot download files off the internet from my current location (Corporate security policy), but can from my home computer later tonight.

[mindbender]

I don't think that returning two different types of data in the same column is a good idea. As matter as fact, it kind of violates rules of data normalization.

I think we would have to have a better idea of your data structure, and what exactly it is you are trying to accomplish (and why). There may be other ways of going about it.

Note that there is the ability to upload a copy of your database here. That often goes a long way in showing a lot of that information.
If you do decide to do that, please be sure to remove any sensitive data.
Also note that I, personally, cannot download files off the internet from my current location (Corporate security policy), but can from my home computer later tonight.

Here is what I am trying to do. I have a file that imports into my database. It contains dials and talktime data including extension number of the rep. I then add manager, rep and team to the data. I use a module (export_daily) that runs and creates a separate excel spreadsheet for each manager and puts it in a folder. Then I run another module (update_Split_daily) that opens each spreadsheet, bolds the first row, autofits the columns, formats the talktime colums and then subtotals all the dial and talktime columns.

By formatting the data from within the excel spreadsheet I was able to get around the formatting of it in the queries, but still have issues.

Each day I get the previous days data so the spreadsheets columns will vary each day and formatting the talktime columns and subtotaling all the columns in the array fails. I think it is because they are all not populated yet.

I attached the db, a copy of the flat file as it is exported from module export_daily and what I would like the spreadsheet to look after module update_Split_daily is run. I hope this makes sense.

Thank you for taking the time to help me outoutputs.zipdb.zip

[JoeM]

I will try to download the files and take a look at it tonight or this weekend, if no one else has a chance to do so first,

[June7]

Don't understand why you are aggregating qry_Monthly_Export

I changed query to:

SELECT TALKTIME.Manager, DIALS.TW_Program_Code, [DIALS]![VSE_FirstName] & " " & [DIALS]![VSE_Surname_Name] AS REP, [DIALS].[Period] & " " & [FldName] AS Expr2, IIf([FldName]="DIALS",CStr([DIALS].[DIALS]),Format([TALKTIME].[TT]/86000,"hh:nn:ss")) AS TheValue INTO EXPORT_TEMP
FROM tblXtabColumns, TALKTIME INNER JOIN DIALS ON (TALKTIME.[Manager] = DIALS.[Manager]) AND (TALKTIME.Period = DIALS.Period) AND (TALKTIME.VSE_Surname_Name = DIALS.VSE_Surname_Name) AND (TALKTIME.VSE_FirstName = DIALS.VSE_FirstName) AND (TALKTIME.TW_Program_Code = DIALS.TW_Program_Code);

This results in string data in TheValues field for both data elements.

Then try First instead of Sum in the CROSSTAB.

[mindbender]

Is there a way to then format the them from text to Number for the dials and [h]:mm:ss for the talktime so when they are subtotaled they add up?

[June7]

At what level do you want to subtotal? The CROSSTAB is grouped down to the Rep. There is nothing to Sum at the Rep level. There is only one Dials and one TT for each Rep for a date.