IIF statement with ISNULL


G

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???
 
Ad

Advertisements

T

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
 
G

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.....


tina said:
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


Tasha said:
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???
 
Ad

Advertisements

T

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


Tasha said:
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.....


tina said:
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


Tasha said:
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???
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top