Formatting Help

[one3nine0]

I have this expression in an Access Query (2013):

DurationSeconds: Format(Nz(Sum([Call Database].[DurationSeconds]),0),"General Number")

But when I run the query, the format of the number is short text. Is there a way to get it so that the format is a number after the query runs?

 

[Micron]

Try reversing the order of the Nz and Sum functions:
Format(Sum(Nz([Call Database].[DurationSeconds],0)),"General Number")

 

[one3nine0]

Try reversing the order of the Nz and Sum functions:
Format(Sum(Nz([Call Database].[DurationSeconds],0)),"General Number")

Nope, that did not work. For what its worth, I also changed the property sheet format for each column to general number as well, and it still formats as short text after the table is made.

 

[one3nine0]

I also tried using Cint function instead of format, but that deleted some of the records instead of just converting them to a number.

 

[Micron]

after the table is made

So this a make table query? You didn't say that. I'm pretty sure the format function won't 'convert' a data type for this purpose. Also, my recollection is that any conversion function (CInt, Cdate, etc) will fail when it encounters a null, so you must have disabled the error reporting or have handled this type of error. At least I know it fails in code. Anyway, that would explain the fewer records. FYI: I created a small table with one field as text and a query with both versions of the expression and got the same result. My thought was that it would be better to convert a null to zero then sum it rather than try to sum a null and convert it. I guess Access is OK with either approach.

Assuming this is a make table query, I've read before that you have to use query tricks or VBA to create a table on the fly with specific data types that differ from the source. Not sure, but this may only apply when the source data is the wrong data type, because I've used make table (mt) queries and ended up with the same data type in the new table. However, I do not use mt queries except only to create the table. After that, I use append/delete/update queries on it. Many think it is bad form to constantly overwrite tables - myself included. So if you cannot convert the source data and must use mt, check out this link or Google it some more (ms access make table query data types).

Add and define a new field in a Make Table query. - Microsoft Community

 

[one3nine0]

I figured it out. There is a function in access val() that will convert whatever into number format. I really don't understand why there are some many functions that seemingly do the same thing but have nuances to them that make it confusing as hell. Thanks for trying tho.

 

[Micron]

These functions are all designed to do different things, but depending on the input, the results may appear to be the same. As noted, a conversion function attempts to convert one data type to another. Format should be thought of as changing the look of the input, or returning a portion of it (as in a date value) but can't be relied on to convert a value. The Val function is supposed to extract what look like numbers from a string so Val(" 12-10 apple ") should return "1210". You should watch out for this because you referred it to a conversion function, but I'm pretty sure it is not. Anyway, I say 'supposed' to because if I recall, it drops leading zeros, but I guess it would be possible to keep them if Val was wrapped in the CDbl function which you'd wrap in the format function. Can get complicated, for sure.