Formula field not showing result for all rows

[bubai]

Hello there,
My Material table has MaterialName and YearlyImportableLimit fields and Import query has MaterialName and ImportedQty fields. I made a query where I deduct ImportedQty from YearlyImportableLimit and the balance is my RemainingImportLimit. But the problem is that not all the material has ImportedQty value. Because some of the materials has not yet imported at all. Hence when I do the deduction, RemainingImportLimit field shows result for only those materials that has value in the ImportedQty field and the rest of the rows remains blank. Whereas those blank rows should have shown the full vlaue for YearlyImportableLimit as there was no deduction.
Any help please?

[pbaldy]

Try using the Nz() function around the import field.

[bubai]

Thanks! I wrapped the furmula with NZ () function, like:

RemainingImportLimit: NZ([YearlyImportableLimit]-[Import]![ImportedQty],[Material]![YearlyImportableLimit])

And it worked smooth. However I seem not be able to format the Result of this field (eg comma separator). Can't I format this as Currency, Date etc.? Or is it a string value it is returning? Any workaround this.

[June7]

Using Nz() in the query does cause the textbox formatting to not be available, however, using it in textbox expression the formatting is available. Weird.

[bubai]

Ok. Cheers!