Sum(iif in access

[KHALID115]

=sum(iif(position="driver" and "labour" and "civil enginner" and "project manager" and "manager",1,0))

this submission is false .
What is the true submission ?

Please help me

[Stewart Ross]

You are assuming that the conditions you have joined with 'and' apply to the field 'position' in the first part of the statement

position = "driver" and "labour" and "civil engineer" and ...

but that is not so. Each test must explicitly state what field or expression you are testing. Also, I am sure you are wrong in using 'and' in your tests, as the IIF statement would never evaluate to True - the field position can never take the value 'driver' AND at the same time 'civil engineer' for instance. You must surely be meaning OR instead:

IIF((position = 'driver') OR
    (position = 'labour') OR
    (position = 'civil engineer') OR
    (position = 'project manager') OR
    (position = 'manager'), 1, 0)

There is a much simpler way to do this however, using an IN statement instead:

IIF(position IN ('driver', 'labour', 'civil engineer', 'project manager', 'manager'), 1, 0)

For clarity I have left out the Sum statement that your IIF is within - you need to get the IIF itself working right first, before you wrap the SUM around it:

=sum(IIF(position IN ... ))

-Stewart