Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Calculated control returning no answer . . . sometimes

Status
Not open for further replies.

relewis

Programmer
Apr 30, 2001
12
US
I have a calculated control on a form which is calculated using this formula:

Code:
=([TotalItemInventory]-nz([Text16]))+nz(DSum("[numberreturned]","[tblEqptReturn]","[productid] = forms![products]![productid]"))

[Text16] is another calculated control on the form which reflects a calculated sum on a subform on this form. [Text16] is caculated with this formula:
Code:
=nz([frmProductOrders Subform].Form!Text9)

All this works fine except when, as I move thru the records on the Products form, if there are no concommitant records in the subform (the subform is empty), there is no sum on the subform for [Text16] to reflect and and I get an error message. I use the nz function to try to convert null answers for [Text16] to zero, but that doesn't seem to help either.

Does anyone know why this is happening? Thanks for any help.

Robin
 
if you use the nz without a value to be replaced, the value replaced is a blank value. You can't do math calculations on blank. I would put zero as the replacement value.
 
Don't you have to change this:
DSum("[numberreturned]","[tblEqptReturn]","[productid] = forms![products]![productid]")
by this ?
DSum("[numberreturned]","[tblEqptReturn]","[productid]=[highlight]" & [/highlight]forms![products]![productid])

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Thanks hneal98 and PHV. I'll give both your suggestions a try out tomorrow morning and report

Robin
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top