Calculated field in web table with null values

**Bryan Daniels** · 07-08-2016, 09:56 AM

Hi all,

I don't normally do a calculated field in a table, but I know its possible. I am storing the value and then showing it on reports.
Here's the problem: I can't get past the empty fields. In a non-web form, Nz works awesomely. Its apparently "invalid" for web stuff though. (Note: I've built many websites, but this is my first web compatible.) I've tried Iif... same result. Here is what I'm trying to accomplish:

= [SubPart 1 Qty] * [SubPart1 Cost] + [SubPart 2 Qty] * [SubPart2 Cost] + [SubPart 3 Qty] * [SubPart3 Cost] + [SubPart 4 Qty] * [SubPart4 Cost] + [SubPart 5 Qty] * [SubPart5 Cost] + [SubPart 6 Qty] * [SubPart6 Cost] + [SubPart 7 Qty] * [SubPart7 Cost] + [SubPart 8 Qty] * [SubPart8 Cost]

Also, I do not want to simply use a default value of 0 in the fields, though that solves it right off. Looks horrible on a form!!

Thanks for your help!
Bryan

**pbaldy** · 07-08-2016, 10:16 AM

The first option in my opinion is fixing the design so you don't have Nulls to begin with:

Fundamentals of Relational Database Design -- r937.com

specifically the repeating fields. They should be in a related table, one record for each SubPart.

**Bryan Daniels** · 07-08-2016, 10:28 AM

Except this is a Bill of Materials form which uses a combobox (Subpart 1) to populate the cost field. Quantity is then added by the user.

**John_G** · 07-08-2016, 10:36 AM

Did you consider using conditional formatting, with white-on-white for zeros? That would allow you to use zeros to avoid nulls.

**pbaldy** · 07-08-2016, 10:38 AM

I don't see how that's relevant to my point. The table is not normalized. If you want to stick with it, you'll likely have to use IIf() on each field, as if memory serves I don't think Nz() works in a calculated field.

**Bryan Daniels** · 07-08-2016, 11:20 AM

John, Though it is a viable option, its inherent problem is that the zeros are still there, you just can't see them. Quantity is entered once the part is selected. When you click in the field, the zero is hiding on the right. When I entered "2" I ended up with '20"!

**Bryan Daniels** · 07-08-2016, 12:05 PM

Paul, sorry, but I'm not quite getting your point. Here's what I have:

I have a "Products" table containing my parts along with other relevant information, including cost. I have a form called SubBomEntry that I create a sub-Bill of Materials with. In that form, I have 8 lines with a combobox, Quantity and Cost. The user drops the combobox to choose a part from the Products table, which then populates it and the cost. The user then enters the quantity.
I used a set 8 lines, because that is the max parts in any sub-BoM. Seldom, though, will they use all eight, hence the null issue. I want to show the calculated total in the form, so they can see it as they build the form AND have it show up in the report.
The rest of the form is a user created Part Number for the sub-BoM, and Routing. All of this information is then stored in the SubBoMs table.

I hope this better explains what I'm trying to accomplish!

**pbaldy** · 07-08-2016, 12:19 PM

In the link, about half-way down, see figure 7 and the following text. Properly normalized, you could have 1 part or 1,000. The design will handle them seamlessly.

**John_G** · 07-08-2016, 12:22 PM

Since the formula you show in your original post is being used on a form, to calculate the total, then you will have no problem using the Nz function to deal with the Nulls.

change [SubPart 1 Qty] to nz([SubPart 1 Qty],0) for all eight quantity fields. You might have to do the same for the Price fields too - what happens if the user does not select a part from a combo box - what is in the Price field?

**Bryan Daniels** · 07-08-2016, 01:36 PM

John, thanks, but it does not solve the mystery of how to store it to the table for future use. You are absolutely correct in that the Nz function does work on the Forms and the Reports. I've gone ahead and created the field in both my form and my report and it works great. In reality, I shouldn't store a calculation like this in a table anyway, because when a cost changes, the stored calculation won't. Again, I appreciate your insights and for giving me ideas to work with.

**pbaldy** · 07-08-2016, 02:31 PM

Like I said, you can use IIf() in a calculated field:

IIf(IsNull(FieldName), 0, FieldName)

but I would not recommend going forward with your design.