If then Else in Query

[turtle]

I am having a hard time doing the following in a query.

I need my field "EarnedValue" to return the result depending on other
fields.

The code in VB would look like this:

If [sumofhrs] > 0 and [Complete] = false then
[earnedvalue] = [sumofbudget] *.5
elseif [complete]= true then
[earnedvalue] = [sumofbudget]
else
[earnedvalue] = 0
Endif

I need to get this EarnedValue returned on the query since I need to
use this calculation on a report.
Can this be done.
If I need to call it as a function then I will need even more help
(never done that)
thanks
KO

[pks]

In queries the syntax is:
iif({condition to evaluate},{value if condition is true},{value if
condition is false})

Note that the braces are not used; I only included them for emphasis.
IIf functions (yes, that's 2 "I"'s) can also be nested. Your entry in
the query grid would be:

EarnedValue: iif([sumofhrs] > 0 and [complete] = false,[sumofbudget] *
0.5, iif([complete] = true, [sumofbudget],0))

You can also show this as currency by using the Format function:
EarnedValue: format(iif([sumofhrs] > 0 and [complete] =
false,[sumofbudget] * 0.5, iif([complete] = true,
[sumofbudget],0)),"currency")

[fredg]

On 20 Apr 2005 13:06:31 -0700, turtle wrote:

I am having a hard time doing the following in a query.

I need my field "EarnedValue" to return the result depending on other
fields.

The code in VB would look like this:

If [sumofhrs] > 0 and [Complete] = false then
[earnedvalue] = [sumofbudget] *.5
elseif [complete]= true then
[earnedvalue] = [sumofbudget]
else
[earnedvalue] = 0
Endif

I need to get this EarnedValue returned on the query since I need to
use this calculation on a report.
Can this be done.
If I need to call it as a function then I will need even more help
(never done that)
thanks
KO

You can use nested IIf() statements.

Add a new column to the query grid.

EarnedValue:IIf([sumofhrs] > 0 and [Complete] = false,[sumofbudget]
*.5,IIf([complete]= true,[sumofbudget],0))

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.