Embedding sum/count within IIF

[karlmcauley]

Hello,

Am i being silly or can you not have an embedded sum within an II
statement in access? For instance:
IIf([Service]="OUT",Sum([Hours]),Null)

So if service = OUT sum the hours that are associated with tha
service.

The query is grouped also.

Any help would be appreciated.

Cheer

 

[karlmcauley]

Hello,

Surely someone out there must know if this is possible....can anyone
help or shed any light on this subject? is it the syntax, can it be
done?

Please help!!

Cheers

 

[Steve Schapel]

Karl,

You could do this...
Sum(IIf([Service]="OUT",[Hours],Null))

Alternatively, not being clear about your purpose, but am I reading
correctly that this expression is a calculated field in a query, and
that the query is grouped by the Service field? So would it work just
to use Sum([Hours]) and put "OUT" as Criteria of Service?

 

[John Vinson]

Hello,

Surely someone out there must know if this is possible....can anyone
help or shed any light on this subject? is it the syntax, can it be
done?

Yes it can be done.

Since you don't say what you've tried (or at least I haven't seen the
message on my news server) I can't say just how but the syntax of IIF
is

IIF(<logical expression>, <value to return if True>, <value if false>)

Any of these three can be a constant, a variable, or a function call;
in particular, either of the values to return could be a call to the
DCount or DSum functions.

The Count and Sum operators in SQL will NOT work in this context,
because IIF() is a VBA function and recognizes only VBA constants,
variables, or functions - not embedded SQL strings.

 

[karlmcauley]

Steve,

Many thanks your reply has solved the issue, something so simple ha
caused me a few days of grief!!!

Just to explain my self a bit further;

[Service] & [Hours] are two separate fields in my Charter_Care table
There are four different services provided by charter care each o
which can have different hours. A person can have one or more service
If i do a query simply summing the hours i may get one person repeate
four times as they have four different services with four sets o
hours. What i wanted was to have one person but with four fields t
their name detailling each service with the hours for each service!!
could have done this is sql + using decodes but alas i didnt know ho
in access.

But many thanks for your help and i will try to explain myself a bi
better next time.

Cheer

 

[¿µ¼±]

Hello,

Am i being silly or can you not have an embedded sum within an IIF
statement in access? For instance:
IIf([Service]="OUT",Sum([Hours]),Null)

So if service = OUT sum the hours that are associated with that
service.

The query is grouped also.

Any help would be appreciated.

Cheers