Help with an Expression please

[chrisline90]

Hi,

I am looking for a little help if poss please?

I have an Access 2010 Report and I am looking to SUM a column of data, but only if data in a different corresponding column is present.

To explain

I have a Column for the 'Date', another for 'NoItems' and another for 'Time'

So,

Date.......NoItems.........Time
---------------------------------
1/8..........1............10:31
1/8..........2
1/8..........4............11:42
1/8..........7............11:56
1/8..........3

I am looking for an expression that will SUM the number of Items, but only if the corresponding Time column has a value in it (all the times are different).

My thoughts are something along the lines of:

=Sum([NoItems])When([Time])IsNotNull

I know the above is not right, but just to give you an idea of my thought process and what I am trying to get at.

Any help at all on this issue would be very greatly accepted

 

[dhookom]

Try

=Sum( ( IsNull([Time]) + 1 ) * NoItems)

Duane
Hook'D on Access
MS Access MVP

 

[chrisline90]

Hi dhookom,

Thank you very much for your response and advice. Your expression works perfectly!