IIF and NZ

[dixxy12]

Hi,

I am trying to return a "0" when not data is available and having trouble doing so.

Here is what i am working with in the control source of the unbound text feild:

=IIf(IsNull([qreCommitedMat]![SumOfQtsOptimized]),"0",[qreCommitedMat]![SumOfQtsOptimized])

it works where there is data, but gives me the famous #error when there is no data.

What do i have wrong?

Thanks

 

[TheAceMan1]

How are ya dixxy12 . . .

Try this:

[blue]=Nz([qreCommitedMat]![SumOfQtsOptimized],0)[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[SkipVought]

Hi,

Also, BIG diffeence between a "0" and 0.

I would assume, that [SumOfQtsOptimized] is NUMERIC and not STRING. Can't mix them!

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[dixxy12]

either case still the same...

 

[TheAceMan1]

dixxy12 . . .

My post didn't work either?

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[dixxy12]

no it still gives the #error...

 

[JoeAtWork]

Check if SumOfQtsOptimized by itself is returning the #Error.

 

[dixxy12]

i think i need to put either an iif or nz in the query feild so that the query does the work not the text feild...but not sure what syntax to use to acheive this....

 

[PHV]

What about replacing this:
=IIf(IsNull([qreCommitedMat]![SumOfQtsOptimized]),"0",[qreCommitedMat]![SumOfQtsOptimized])
with this ?
=Nz("SumOfQtsOptimized","qreCommitedMat",0)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dixxy12]

that in the text feild's control source gives me a:
'The expression you entered has a function containing the wrong number of arguments.' error.

 

[PHV]

OOps, sorry for the typo:
=Nz(DLookUp("SumOfQtsOptimized","qreCommitedMat"),0)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dixxy12]

i still get the #error with this...

 

[PHV]

What is the SQL code of qreCommitedMat ?

 

[dixxy12]

SELECT Sum(IIf([QtsOptimized],[QtsOptimized],"0")) AS qts, tblInventoryCommited.Done, tblInventoryCommited.ItemNumber
FROM tblInventoryCommited
GROUP BY tblInventoryCommited.Done, tblInventoryCommited.ItemNumber
HAVING (((tblInventoryCommited.Done)=False) AND ((tblInventoryCommited.ItemNumber)=[Forms]![frmPurchaseOrders]![frmTabs].[Form]![ItemNumber]));

 

[PHV]

So, there is no SumOfQtsOptimized field in qreCommitedMat ...

 

[dixxy12]

yes...i would like it to give ma a 0 (zero) when no data is available

 

[JoeAtWork]

Then use your Nz on a field that actually exists in your query.

 

[randy700]

You do not have a field called SumOfQtsOptimized in your query, so you CANNOT use it in calculating the value of a text box.

Try replacing SumOfQtsOptimized with qts, which is the alias you used in your query - Sum(IIf([QtsOptimized],[QtsOptimized],"0")) AS qts


Randy