Summing calculations in form (1 Viewer)

[LoveBard]

Hi, to start, let me start by qualifying this query with the fact that I have very rudimentary Access skills (any other Microsoft application I am fine with).

I am using a template from Microsoft in Access to create an asset/inventory database. I have an assemble asset form in which I have added a tab that does Cost of Goods calculations (everything that goes into the asset from inventory).

The calculation uses the following fields for each row/item line:

• Combo box (look up inventory item from inventory table)
• Cost per item (Dlookup of inventory item's cost per item from inventory table)
• Number of items used (manual entry of a number)
• Total cost per item (calculation of CPI*#items used)

I have 16 rows to allow for multiple items (these are furniture pieces with many parts)

Each row calculates fine. I have then added a total box at the bottom of the column of total cost per item. It only works if every row is filled in with data. If I only add two rows of data, the box remains blank. Once all rows are filled in, it adds the amounts.

This is the expression I used: =[TCPI1]+[TCPI2]+[TCPI3]+[TCPI4]+[TCPI5]+[TCPI6]+[TCPI7]+[TCPI8]+[TCPI9]+[TCPI10]+[TCPI11]+[TCPI12]+[TCPI13]+[TCPI14]+[TCPI15]+[TCPI16]

Is there a way to add to the calculation an ignore if blank command like Excel has? (and is there a way to say TCPI1:16 rather than add each box individually?

 

[plog]

Yes, use the Nz function:

MS Access: Nz Function

This MSAccess tutorial explains how to use the Access Nz function with syntax and examples. The Microsoft Access Nz function lets you return a value when a variant is null.

www.techonthenet.com

 

[LoveBard]

Sorry, but I don't really know how to write that. I tried: =[TCPI1]+[TCPI2]+[TCPI3]+[TCPI4]+[TCPI5]+[TCPI6]+[TCPI7]+[TCPI8]+[TCPI9]+[TCPI10]+[TCPI11]+[TCPI12]+[TCPI13]+[TCPI14]+[TCPI15]+[TCPI16]=Nz(0,0)
And it only returns zero as the answer when all rows are filled in and does not help with the problem of summing without all field entered.

I know I am not writing it correctly, but I don't really understand what a variant is or what to use.

 

[bastanu]

You need to wrap each individual control in the Nz function:
Nz( TCPI1) + Nz(TCPI2)+....

 

[LoveBard]

Thank you, that worked