Can anyone translate this SQL statement for me?

Something in this below query is not returning all the values I need, so I
need to examine it a bit further to see what it is doing. I don't know IIf
statements in SQL too well, so any translation of the below would be very
helpful.

IIf(IIf([CRMAST]![Points_Owned] And [CRMAST_1]![Points_Owned],[T4: Agg
Sales]![Total
Points]-[CRMAST]![Points_Owned]-[CRMAST_1]![Points_Owned],IIf([CRMAST]![Points_Owned],[T4:
Agg Sales]![Total
Points]-[CRMAST]![Points_Owned],IIf([CRMAST_1]![Points_Owned],[T4: Agg
Sales]![Total Points]-[CRMAST_1]![Points_Owned],[T4: Agg Sales]![Total
Points])))0,0,1)

I really just can't figure out the beginning AND part of
"IIf([CRMAST]![Points_Owned] And [CRMAST_1]![Points_Owned]". To me this says
if valueX from table1 and valueX from table2... but doesn't give a condition,
such as = or .

Thanks for any help!
Todd

In VBA, True is usually defined as -1 and False as 0, but in actual fact,
any non-zero value will be treated as True.

Assuming [Points_Owned] is a numeric field, the IIf statement will execute
the True portion if both [CRMAST]![Points_Owned] And
[CRMAST_1]![Points_Owned] are non-zero, and the False portion if either (or
both) are zero.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"tmwilkin" wrote in message
...
Something in this below query is not returning all the values I need, so I
need to examine it a bit further to see what it is doing. I don't know
IIf
statements in SQL too well, so any translation of the below would be very
helpful.

IIf(IIf([CRMAST]![Points_Owned] And [CRMAST_1]![Points_Owned],[T4: Agg
Sales]![Total
Points]-[CRMAST]![Points_Owned]-[CRMAST_1]![Points_Owned],IIf([CRMAST]![Points_Owned],[T4:
Agg Sales]![Total
Points]-[CRMAST]![Points_Owned],IIf([CRMAST_1]![Points_Owned],[T4: Agg
Sales]![Total Points]-[CRMAST_1]![Points_Owned],[T4: Agg Sales]![Total
Points])))0,0,1)

I really just can't figure out the beginning AND part of
"IIf([CRMAST]![Points_Owned] And [CRMAST_1]![Points_Owned]". To me this
says
if valueX from table1 and valueX from table2... but doesn't give a
condition,
such as = or .

Thanks for any help!
Todd

The assumption would be that those two fields are boolean (yes/no)
fields. So if both are true then the expression is true, otherwise the
expression is false.

That could be written more clearly as
CRMAST.Points_Owned = True and CRMAST_1.PointsOwned = True,
However as written it should work.



'================================================= ===
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'================================================= ===


tmwilkin wrote:
Something in this below query is not returning all the values I need, so I
need to examine it a bit further to see what it is doing. I don't know IIf
statements in SQL too well, so any translation of the below would be very
helpful.

IIf(IIf([CRMAST]![Points_Owned] And [CRMAST_1]![Points_Owned],[T4: Agg
Sales]![Total
Points]-[CRMAST]![Points_Owned]-[CRMAST_1]![Points_Owned],IIf([CRMAST]![Points_Owned],[T4:
Agg Sales]![Total
Points]-[CRMAST]![Points_Owned],IIf([CRMAST_1]![Points_Owned],[T4: Agg
Sales]![Total Points]-[CRMAST_1]![Points_Owned],[T4: Agg Sales]![Total
Points])))0,0,1)

I really just can't figure out the beginning AND part of
"IIf([CRMAST]![Points_Owned] And [CRMAST_1]![Points_Owned]". To me this says
if valueX from table1 and valueX from table2... but doesn't give a condition,
such as = or .

Thanks for any help!
Todd