Access 2007 Records Totals sum missing from List box

[agent86oz]

Hi I have a very basic phone records file which I download and run a
query on. I have a couple of expressions for formating and calculation
to make the data show in minutes. It all looks great until I select
the totals button in the records which gives a row at the bottom of
the query of Total.

If you run across the row each column has a List Box with None, Sum,
Count, Maxinum etc.

The problem is colums which are created with an Expression only have
None or Count.

I find the same issue when trying to create a report.

An example expression is

Total Time Minutes: Format([Total Duration]*1440,"#")

The calculation and format is fine but I loose the option of Sum which
I am looking for.

 

[John Spencer]

Using FORMAT turns the number into a STRING. Strings cannot be summed,
averaged, etc.

Try using one of the following expressions instead. They all return numbers,
not strings.

Total Time Minutes: Int([Total Duration]*1440)
Returns only the integer portion of the number (no rounding)

Or
Total Time Minutes: Round([Total Duration]*1440),0)
Rounds up or Down

Or
Total Time Minutes: CLng([Total Duration]*1440)
Rounds up or down but can't handle null (blank) values


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[agent86oz]

Using FORMAT turns the number into a STRING.  Strings cannot be summed,
averaged, etc.

Try using one of the following expressions instead.  They all return numbers,
not strings.

Total Time Minutes: Int([Total Duration]*1440)
  Returns only the integer portion of the number (no rounding)

Or
Total Time Minutes: Round([Total Duration]*1440),0)
  Rounds up or Down

Or
Total Time Minutes: CLng([Total Duration]*1440)
  Rounds up or down but can't handle null (blank) values

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Hi I have a very basic phone records file which I download and run a
query on. I have a couple of expressions for formating and calculation
to make the data show in minutes. It all looks great until I select
the totals button in the records which gives a row at the bottom of
the query of Total.

If you run across the row each column has a List Box with None, Sum,
Count, Maxinum etc.

The problem is colums which are created with an Expression only have
None or Count.

I find the same issue when trying to create a report.

An example expression is

Total Time Minutes: Format([Total Duration]*1440,"#")

The calculation and format is fine but I loose the option of Sum which
I am looking for.- Hide quoted text -

- Show quoted text -

Hey that was great. I have another additional issue

Total Time Hours Minutes: Format([Total Duration],"Short Time")

I tried INT in place of Format but have an error.

Is there a good site for tutorials on this stuff as I am fairly new,
Mostly a spreadsheet person, would save me asking basic questions.

Thanks