By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,471 Members | 2,277 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,471 IT Pros & Developers. It's quick & easy.

If then Else in Query

P: n/a
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

Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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")

Nov 13 '05 #2

P: n/a
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.
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.