Sum function with zero subtotal

[ghatton]

I need help with a continuous form. I want the subtotal in the form footer to read $0.00 if the query returns no results.Currently the total is blank when the query returns no results.
How do I fix this (I want to use the subtotal later on a master form)
Thanks in advance.

 

[DougP]

are you using
IIF([your total field] = is null,&quot;$0.00&quot;,[your total field]) [sig]<p>DougP, MCP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.[/sig]

 

[ghatton]

Here's what I tried, but I still keep getting a blank field when I run the form:

=if(Sum([price])=Null,0,Sum([price]))

I keep thinking that it has to do with the format of the field, but it seems not to work.

Thanks.

 

[TheInsider]

Hi,
If you pasted your code correctly, your problem could be the fact that you're calling the &quot;IIf&quot; function with &quot;If&quot;. Add the extra &quot;I&quot;. Also, the following is more efficient and can replace your IIf statement:

=Nz(Sum([Price]), 0)

[sig]<p>Rob Marriott<br><a href=mailto:rob@career-connections.net>rob@career-connections.net</a><br>[/sig]

 

[ghatton]

Thanks for the info. I did leave out the extra 'I' in the iif statement when I wrote my post, but I had it in the actual statement.

I tried using the NZ function but got the same result anyway.

Basically, how do you return 0 if the query returns no results. Maybe I'm going about it the wrong way?

Thanks,

Grant.

 

[MichaelRed]

If the 'query' doesn't return any results, the field isnt NULL, it just doesn't exist. You need to check the recordcount property of the query AS WELL as the null/o value of the field (but check recordcount first. IF recordcount = 0, then (obviously?) the field (& value) don't exists, therfore the (?appropiate?) value would be 0.


[sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]

 

[ghatton]

Michael,

That answer makes total sense. However, I can't find any reference or instruction on HOW to check the recordcount property of the query.

I thank you all for the help.

Grant.

 

[MichaelRed]

it (recordcount) is a property of a recordset object. You need to define/instantiate the recoedset (based on your query), move to the last record (rst.movelast) and get the recordcount (MyNumber = rst.recordcount)

Private MyRecCount () as Long

[tab]Dim Dbs as database
[tab]Dim rst as recordset

[tab]Set dbs = currentdb
[tab]Set rst = db.openrecordset(&quot;your query name here&quot

[tab]rst.Movelast
[tab]If (rst.Recordcount > 0) Then
[tab][tab]'Set the subtotal value from the query
[tab] Else
[tab][tab]'Set the subtotal value to Zero
[tab]End If

End Function

This is very quick and dirty (just freehand typed into the reply box), so look up any thing which doesn't look right or work
[sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]

 

[ghatton]

Hi,

Fixed that problem by using a =iserror function on the master form. I created two controls, one that returned an 0 or -1 based on whether one control was an error. The other control used an if statement to pull values from two different controls based on the iserror statement.

Thanks for everybody's help.

Grant. [sig][/sig]