Iif statement in Access

rangeview · March 19th, 2004, 01:11 AM

I am trying to teach myself Access and can't figure out an Iif statement. I have a field called Status, which uses either P, S, NS, R, or W. I want to calculate the sum of a field called BMUSbmTtl, but only if [Status] <> W. I've tried =Iif([Status]<>"W",sum[BMUSbmTtl])). I get a calculation, but it still calculates all records, including those where status =W.

What am I doing wrong?

Thanks for your help--ms

joefawcett · March 19th, 2004, 04:28 AM

The inline if function needs three parts, an expression to be evaluated as True/False and two statements, the first to return if the expression is True, the second if not. Yours has only one value, there is no value to return or action for the second part.

--

Joe

Steven · March 21st, 2004, 05:36 PM

So, to put it into your context:

=Iif([Status]<>"W",sum([BMUSbmTtl]),-1)

That will give you -1 for cases where status=W

I am a loud man with a very large hat. This means I am in charge

rangeview · March 21st, 2004, 05:43 PM

Thanks, Steven, but wouldn't the -1 change the outcome of the addition? What I really want the "else" to do is just skip the calculation on that line if it doesn't meet the "W" criteria.

Steven · March 22nd, 2004, 04:55 PM

Unless you're summing that entire expression, it won't change the sum.
So unless you have something like
=Sum(iif([Status]<>"W",Sum([BMUSbmtbl]),-1))
it will work OK.

If you want to have nothing show up, change the -1 to Null
so you'd have:

Code:

=Iif([Status]<>"W",sum([BMUSbmTtl]),Null)

I am a loud man with a very large hat. This means I am in charge

Steven · March 22nd, 2004, 04:57 PM

To clarify even more:

that whole statement, if written into a module, would be:

Code:

If [Status]="W" Then
   intVariable = Sum([BMUSbmtbl)
Else
   intVariable = -1
End If

So it would return either the Sum, or -1

But Null should do you

I am a loud man with a very large hat. This means I am in charge

skhall · March 24th, 2006, 07:26 PM

Steven,
I am trying to teach my students how to write IIf functions in queries. Can you tell me how to write IIF that will figure the gross pay for employees that work <=40 Hours or for those that work over 40 hours? Thanks!

Sherry Kay

kindler · March 28th, 2006, 01:14 PM

IIf([HoursWorked]<=40,40*HourlyRate,40*[HourlyRate]+([HoursWorked]-40)*[OTRate])