trouble understanding expression

[mejia.j88]

hi everyone,


I have been asked to look at a DB from work, but i am having trouble understanding the following expression:

Conflict: IIf((Sum(IIf([Drafting Schedule]![Due Date]<=[Dafting Sch Qry]![Due Date] And [Drafting Schedule]![Due Date]>=[Dafting Sch Qry]![Start Date] And [Drafting Schedule]![Project Name]<>[Dafting Sch Qry]![Project Name] And [Drafting Schedule]!Operator=[Dafting Sch Qry]!Operator,Yes,No))<>0) And (Sum(IIf([Drafting Schedule]![Due Date]<=[Dafting Sch Qry]![Due Date] And [Drafting Schedule]![Due Date]>[Dafting Sch Qry]![Start Date] And [Drafting Schedule]!Operator=[Dafting Sch Qry]!Operator,[Drafting Schedule]![Estimated Hours],0))>8),Yes,No)

can someone help clarify?
it'd be greatly appreciated.

thanks,
Jm

[June7]

It is a very long IIf that when evaluated should return either Yes or No depending on whether the expression evaluates True or False. What exactly do you not understand about the expression? Is it not working properly?

In brief, it means: IF Sum(a) <> 0 AND Sum(b) > 8 THEN Yes ELSE No

The expression for a is unusual and a bit confusing. It wants to sum Yeses and Nos. The query should read Yes as -1 and No as 0 and the sum will be either a negative or 0.

Sum(IIf(
[Drafting Schedule]![Due Date]<=[Dafting Sch Qry]![Due Date]
And
[Drafting Schedule]![Due Date]>=[Dafting Sch Qry]![Start Date]
And
[Drafting Schedule]![Project Name]<>[Dafting Sch Qry]![Project Name]
And
[Drafting Schedule]!Operator=[Dafting Sch Qry]!Operator,
Yes, No))

This is a little simpler and easier to follow:
IIf(Sum(
IIf([Drafting Schedule]![Due Date] Between [Dafting Sch Qry]![Start Date] And [Dafting Sch Qry]![Due Date]
And
[Drafting Schedule]![Project Name]<>[Dafting Sch Qry]![Project Name]
And
[Drafting Schedule]!Operator=[Dafting Sch Qry]!Operator,
1, 0))
<> 0

And for b:
IIf(Sum(
IIf([Drafting Schedule]![Due Date] Between [Dafting Sch Qry]![Start Date] And [Dafting Sch Qry]![Due Date]
And
[Drafting Schedule]!Operator=[Dafting Sch Qry]!Operator,
[Drafting Schedule]![Estimated Hours],0))
>8

EDIT: aytee111, I was editing my post before noticed yours. We are on same track!

[aytee111]

It doesn't seem to make sense to me, but as June says it is a long expression. I broke it down by ")" and got:

if sum(a) - "a" can be either yes or no (!!!!) - is <> 0
and sum(b) - "b" can either be estimated hours or 0 - is > 8
then the final value will be Yes, otherwise it will be No

IIf(
(Sum
(IIf
([Drafting Schedule]![Due Date]<=[Dafting Sch Qry]![Due Date] And [Drafting Schedule]![Due Date]>=[Dafting Sch Qry]![Start Date] And [Drafting Schedule]![Project Name]<>[Dafting Sch Qry]![Project Name] And [Drafting Schedule]!Operator=[Dafting Sch Qry]!Operator
then,Yes
otherwise,No)
)
<>0)
And
(Sum
(IIf
([Drafting Schedule]![Due Date]<=[Dafting Sch Qry]![Due Date] And [Drafting Schedule]![Due Date]>[Dafting Sch Qry]![Start Date] And [Drafting Schedule]!Operator=[Dafting Sch Qry]!Operator,
then [Drafting Schedule]![Estimated Hours]
otherwise,0)
)
>8),
then Yes,
otherwise No)

[mejia.j88]

one of the things that didnt make sense to me was that the fields for Drafting Sch Qry are pulled from the table Drafting Schedule. does that seem circular?

this expression, nonetheless, is from a different query, titled Drafting Conflict.

when i run my report, i get the Enter Parameter Value msgbox with Conflict as the value that i should be entering for.

the report (and query that runs this report) does not have the conflict field nor does it call the Drafting Conflict Query.

ultimately, i don't understand why that parameter keeps popping up. i figured it had to do something with the Conflict :/

thanks a ton for the breakdown guys

[June7]

I agree, actually all of the conditions seem odd that they are comparing values from a table to a query generated from that table, but then we don't know what the query does.

Also, there must be a reference to Conflict somewhere in the report, the struggle will be tracking it down. I use an add-in called Rick Fisher's Find and Replace when I need to search my project for some obscure reference. Saved my life many times. Costs about $50.