Help on form that has empty query record.

[binbin]

Hello everyone, sorry for the title but I have trouble thinking of a way to out into words what my problem is at the moment.

I have a mainform that has two subforms.

SubformA is like an itemized detail of what is contained in the mainform.

SubformB is based on a query that summarizes SubformA based on what the mainform primary key is currently on.

In SubformB, there is a expression field that gives an amount value, which is then summed by a textbox called txtSumAmount which has a formula of Nz(Sum([Amount]),0) at the form footer.

I then call the value of txtSumamount to a textbox in the mainform called txtRunningTotal to serve as a running total.

Now everything only works if SubformA has records for the query on SubformB to sum. The running total textbox on the mainform displays the correct values.

However if for some reason the user did not add records in SubFormA (which is acceptable) or deletes all of them thereby emptying the sum query of SubformB, I get a blank value from txtSumAmount which displays a #size! error on txtRunningTotal.

How can I make txtSumAmount display zero instead of a blank or null, I don't even know what to call it.

I tried some of this in txtSumAmount so far, nothing has worked:

A) =IIf(IsNull(Sum([Amount])),0,Sum([Amount]))
B) I made another textbox control called txtCount with =Count(*) value at the footer to count records then on txtSumAmount = IIF(txtCount=0,0,Sum(Amount))



I'm currently stuck, does anyone have any suggestions? Thank you for your time.

[pbaldy]

One way:

http://access.mvps.org/access/forms/frm0022.htm

[binbin]

Hi thank you for your reply, however, I still could not make it work since I'm not sure if I'm doing it correctly. I have inserted the code in a module.
I made another control named txtTest and put nnz(Sum[Amount]) on its controlsource which should return a zero. However, it just displays nothing.

I think the solution mentioned is for when the textbox displays an #Error, but mine does not display that, except on the mainform control txtRunningTotal which displays #Size!.
I'm not bothered by control txtRunningTotal value of #Size! on the main form at the moment, since I know that the problem is with txtSumAmount control on the footer of the SubformB which displays nothing when there are no records in SubformB. I need txtSumAmount on the SubFormB's footer to display a numerical zero when there are no records to sum.

I also tried Dcount("*","[tblSubFormA]","[ID]=" & Mainform.ID) This also displays a blank, but when I enter a record in SubFormA, the count will return 1 or is equal to how many records I enter. But if there are none, it only displays blank.

Again, I'm not really sure if I did get the author's solution right but applied it incorrectly, if that is the case, may I have some assistance on that matter?

Thanks again.

Note: Just to point out that SubFormB is a summariztion of SubFormA, the field I'm trying to summarize from SubFormB is [Amount] which is a calculated field that SubFormB summarizes, which in turn is summarized by txtSumAmount in the footer. It works only when there are at least 1 record in SubFormA.

[binbin]

Looking at the current database, I find it to have become too complex to properly troubleshoot, so I made a simpler database and I'm able to replicate the #error display on the textbox, which leads me to believe that the author's solution is indeed applicable and so are other suggestions I found on article searches. Will update soon...

[binbin]

After simplifying the database that sums values, it is now currently working. The answer was in the replication of the #Error display and then I went from there since there are lots of solutions referring to that error. Thanks everyone.

[pbaldy]

Glad you sorted it out.