Access 2007 Records Totals sum missing from List box

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

agent86oz - 25 Apr 2008 10:18 GMT

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.

Reply to this Message

John Spencer - 25 Apr 2008 13:18 GMT

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
[quoted text clipped - 16 lines]
> The calculation and format is fine but I loose the option of Sum which
> I am looking for.

Reply to this Message

agent86oz - 25 Apr 2008 15:38 GMT

> Using FORMAT turns the number into a STRING. Strings cannot be summed,
> averaged, etc.
[quoted text clipped - 40 lines]
>
> - 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

Reply to this Message