alright, here is my problem. I have

[Rmck87]

alright, here is my problem. I have a fairly large query that i am running. It has an IIF statement in it, and if the statement is true then it will print a column from another query, if it is false, then it will print a column from a query. The problem is that whenever i print the column from the query, i need to put some column names from that query in the where clause, when i put these in the where clause, it messes up the IIF statement if it is false. This is my code, broken down:


Select IIf(pm.pm_billing_method_id='F', revenue2.rev, sum(b.hourly_rate*e.hours_actual)) AS Revenue
From dbo_pm_project AS pm, dbo_pm_billing_rate AS b, revenue2, dbo_time_entry AS e
Where ((revenue2.member_id) = m.member_id) and ((revenue2.p.project_id) = pm.project_id))



I need these two columns from the query (Revenue2) to be in the Where Clause. Only when the IIF statement is true, if it is false I dont want these two columns to be in the Where Clause. Any ideas? Thanks for all the help.


-Ryan

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[scriverb]

try creating a UNION query with the following code. If I understand you problem this should give you what you want:

Select revenue2.rev AS Revenue
From dbo_pm_project AS pm, dbo_pm_billing_rate AS b, revenue2, dbo_time_entry AS e
Where (pm.pm_billing_method_id = 'F') AND ((revenue2.member_id) = m.member_id) and ((revenue2.p.project_id) = pm.project_id)
UNION
Select sum(b.hourly_rate*e.hours_actual) AS Revenue
From dbo_pm_project AS pm, dbo_pm_billing_rate AS b, revenue2, dbo_time_entry AS e
Where (pm.pm_billing_method_id <> 'F');

Post back with any questions or problems.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[Rmck87]

Correction:


It has an IIF statement in it, and if the statement is true then it will print a column from another query, if it is false, then it will print a column from a table

thanks

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.