if logic in query need help

[Guest]

im doing this in my query Sum(IIf([SFBIS1]=0,1,0)) this works and say if
the field is equal to zero put a one otherwise 0 is there a way to
say if the field is a 0 or a 2 or a 5 then put a one otherwise 0 how would
i do this?

 

[fredg]

im doing this in my query Sum(IIf([SFBIS1]=0,1,0)) this works and say if
the field is equal to zero put a one otherwise 0 is there a way to
say if the field is a 0 or a 2 or a 5 then put a one otherwise 0 how would
i do this?

Sum(IIf([SFBIS1]=0 OR [SFBIS] = 2 OR [SFBIS] = 5,1,0))

 

[Guest]

Hi!
I would suggest you write a function to determine what result to return.
It's fast, clean and you can keep amending the function if your criteria
changes without having to mess with a long and cumbersome if statement.

My idea would be something like this in a Module...

Public Function ReturnValue(SumResult As Long) As Byte
Select Case SumResult
Case 2, 5
ReturnValue = 1
Case Else
ReturnValue = 0
End Select
End Function

In your query you would do something like ReturnValue(Sum([SFBIS1]))

Hope this works for you!

 

[Marshall Barton]

im doing this in my query Sum(IIf([SFBIS1]=0,1,0)) this works and say if
the field is equal to zero put a one otherwise 0 is there a way to
say if the field is a 0 or a 2 or a 5 then put a one otherwise 0

Sum(IIf(SFBIS1 IN(0,2,5), 1, 0))