Getting invoice to total even with null fields?

[VBjavaFriend]

I am new to Access so please don't laugh, but I am having problems with a simple invoice form I created. I have it set up as a series of textboxes: Quantity, UnitCost, and TotalCost. Quantity is formatted as a general number, and the other two are formatted as currency. I have it set up where there are 14 "lines" (rows of textboxes) in which to enter quantities (Quantity1, Quantity2, etc) and costs (UnitCost1, TotalCost1, UnitCost2, TotalCost2, etc.) My subtotal textbox has the following as the control Source: =([TotalCost1]) + [TotalCost2] + [TotalCost3.....]) I had to set the default property of all of the costs and quantities to 0 because the subtotal and total remains blank if any of the values to be added are null. I don't want all of the textboxes to have zeros in them. What can I do? (I tried to set it up as a control array in a programming module, but access basic didn't like it. (I am used to straight vb.)) Please help me!

 

[TommyTea]

There are a couple of ways to get this to work. Wrap the nz function around each of the elements of your formula (nz([field1])+ nz(]field2])+mz([field3])) or you can use an immediate if statement (IIF([field1] is null, 0, [field1])+IIF([field2] is null, 0, [field2])+IIF([field3] is null, 0, [field3])). Good luck.

 

[JeremyNYC]

Yeah, I'd go with the first solution, not the iifs. Iif are pretty dang slow.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[VBjavaFriend]

I tried it the first way you suggested. It worked great! I still have two problems, however. In order to rid the form of zeros I had to make the quantity textboxes and the unit cost textboxes unbound. Now that they are unbound (maybe it has nothing to do with that fact) I can't create a "next record". Also, I still have the problem of zeros in the total column because all of those textboxes perform a calculation based upon the preceding quantity and unit cost. Is there a way to program it to say if it is zero then show null? Will I ever finish this project? Please help if you can because I have hit a block wall!

 

[JeremyNYC]

You don't need to make your text boxes unbound, you need to change the default value of those fields in your table. And if there are already recrods with zeros that you don't want, do update queries to set those zeros to null.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.