Help with query that has the same table, twice

[ChuckColeman1812]

I have a query that uses two instances of the same table, [Order Table] and [Order Table_1], plus some others. In the attached Word document I renamed [Order Table_1] to [No Split Orders]. Please read the attached Word document and give me some feedback why I can't get the logic for the "field" Yo to work.




I appreciate you guidance!


Chuck

[ssanfu]

First, let me say "Don't use spaces in object names". They are a real PITA!!

OK, to the problem(s).
You said

I have a query that uses two instances of the same table,

If that is so, then the (Word) example cannot be.. I will call the two columns "OrderReported" and "SplitReported". Since they are the *same* table, if one field is true, then both fields are true.
For a given record, you cannot have "OrderReported" = TRUE and "SplitReported" = FALSE (or vise versa), if they are two instances of the same field from the same table.

In the calculation, you are missing the False clause from the third IIF() function.

But the main problem I see is the logic. Lets look at the first condition.

IIf([Order Table].[Reported]=TRUE,"True",

(BTW, IMHO, you should never use "-1" to refer to a TRUE state for a boolean field type. See below)

So if the field [Reported] is TRUE then a string "True" is returned. If [Reported] is FALSE, then look at the next (nested) IIF() function.

The second IIF() says:

IIf([Order Table].[Reported]=FALSE,"False",

if the field [Reported] is FALSE, then return the string "False".

The last two tests for [No Split Orders].[Reported] are useless, because there are only two states for the first field - It can ONLY be TRUE or FALSE. And the only values that will be returned is TRUE or FALSE!!
"True-No Split" and "False-No Split" will NEVER be returned.

-------------------------------------------------
-------------------------------------------------
This is Below..

In ACCESS,
MS has defined a constant named "FALSE" to be equal to the value 0
MS has defined a constant named "TRUE" to be equal to the value -1.

In EXCEL,
MS has defined a constant named "FALSE" to be equal to the value 0
MS has defined a constant named "TRUE" to be equal to the value +1.

You have a calculation that looks like this:

OT: IIF([OverTime]= -1, [Rate]*[Hours],0)

Now lets say that in a SP, MS redefines TRUE to match EXCEL, so that, in ACCESS, now TRUE = 1. What happens to your calculation? Instead of getting Overtime, you get zero.

Same scenario, however this time you use the constant TRUE in the calculation:

OT: IIF([OverTime]= TRUE, [Rate]*[Hours],0)

It doesn't matter what value MS defines as the value of TRUE (except 0, of course).