IIF statement with ISNULL

[Guest]

I am trying to create an expression in a query using TABLE1Y and TABLE2T that
says, If TABLE2.IPQTY - TABLE1.IPQTY is null, then print TABLE2.IPQTY,
otherwise subtract TABLE1.IPQTY from TABLE2.IPQTY. The data in the fields is
numeric, however fields are blank instead of having a 0 if there is no data.
This is the statement I have in my expression

DIPQTY:
IIf(IsNull([TABLE2T]![IPQTY]-[TABLE1Y]![IPQTY]),[TABLE2T]![IPQTY],[TABLE2T]![IPQTY]-[TABLE1Y]![IPQTY])

I'm getting weird results. Can someone help???

 

[tina]

try

DIPQTY: Nz([TABLE2T]![IPQTY],0)-Nz([TABLE1Y]![IPQTY],0)

suggest you read up on the Nz() function in Help, so you'll understand how
it works.

hth

 

[Guest]

Thanks Tina, unfortunately that didn't work. Is there something wrong with
the way I put it in?
DIPQTY:
IIf(Nz([TABLE2T]![IPQTY],0)-Nz([TABLE1Y]![IPQTY],0),[table2t]![ipqty],[TABLE2T]![IPQTY]-[TABLE1Y]![IPQTY])

seems like there is something missing saying that if table2t-table1y
variance is equal to 0, then we want table1y.ipqty field to print, otherwise,
to show the variance. It acts like it is thinking they are all 0's with what
I have in there now and is printing the ipqty field from table2t. I so much
appreciate your help. I did see the nz function in help, but wasn't using it
like you had.....

try

DIPQTY: Nz([TABLE2T]![IPQTY],0)-Nz([TABLE1Y]![IPQTY],0)

suggest you read up on the Nz() function in Help, so you'll understand how
it works.

hth

I am trying to create an expression in a query using TABLE1Y and TABLE2T that
says, If TABLE2.IPQTY - TABLE1.IPQTY is null, then print TABLE2.IPQTY,
otherwise subtract TABLE1.IPQTY from TABLE2.IPQTY. The data in the fields is
numeric, however fields are blank instead of having a 0 if there is no data.
This is the statement I have in my expression

DIPQTY:
IIf(IsNull([TABLE2T]![IPQTY]-[TABLE1Y]![IPQTY]),[TABLE2T]![IPQTY],[TABLE2T]!
[IPQTY]-[TABLE1Y]![IPQTY])

I'm getting weird results. Can someone help???

 

[tina]

hmm, okay, i don't think i caught on to the logic you were attempting.
sounds like you're saying that if the two values are equal (that would give
you a zero value when subtracting one from the other), you want the first
value to display, otherwise display the number resulting from subtracting
the second value from the first value.

if the above is correct, try

IIf([TABLE2T].[IPQTY]=[TABLE1Y].[IPQTY],[TABLE2T].[IPQTY],[TABLE2T].[IPQTY]-
[TABLE1Y].[IPQTY])

and notice that i change the ! bang to a . dot in all instances.

hth

Thanks Tina, unfortunately that didn't work. Is there something wrong with
the way I put it in?
DIPQTY:
IIf(Nz([TABLE2T]![IPQTY],0)-Nz([TABLE1Y]![IPQTY],0),[table2t]![ipqty],[TABLE
2T]![IPQTY]-[TABLE1Y]![IPQTY])

seems like there is something missing saying that if table2t-table1y
variance is equal to 0, then we want table1y.ipqty field to print, otherwise,
to show the variance. It acts like it is thinking they are all 0's with what
I have in there now and is printing the ipqty field from table2t. I so much
appreciate your help. I did see the nz function in help, but wasn't using it
like you had.....

try

DIPQTY: Nz([TABLE2T]![IPQTY],0)-Nz([TABLE1Y]![IPQTY],0)

suggest you read up on the Nz() function in Help, so you'll understand how
it works.

hth

I am trying to create an expression in a query using TABLE1Y and

TABLE2T
that

says, If TABLE2.IPQTY - TABLE1.IPQTY is null, then print TABLE2.IPQTY,
otherwise subtract TABLE1.IPQTY from TABLE2.IPQTY. The data in the

fields
is

numeric, however fields are blank instead of having a 0 if there is no data.
This is the statement I have in my expression

DIPQTY:

IIf(IsNull([TABLE2T]![IPQTY]-[TABLE1Y]![IPQTY]),[TABLE2T]![IPQTY],[TABLE2T]!
[IPQTY]-[TABLE1Y]![IPQTY])

I'm getting weird results. Can someone help???