Null Problem

  • Thread starter Thread starter DS
  • Start date Start date
D

DS

I have a Main Form with a Subform. In the footer of the subform I have
an Expression that works as long as there is a record. If there are no
records I get nothing, I want the total to be 0, I tried the two
following expressions, they only work if there are records. Otherwise I
see nothing. Does anyone know what I'm doing wrong?


=Nz([TotwTax],Sum([TotwTax]),0)
=IIf(IsNull([TotwTax]),Sum(TotwTax),0)

Thanks
DS
 
DS said:
I have a Main Form with a Subform. In the footer of the subform I
have an Expression that works as long as there is a record. If there
are no records I get nothing, I want the total to be 0, I tried the
two following expressions, they only work if there are records.
Otherwise I see nothing. Does anyone know what I'm doing wrong?

=Nz([TotwTax],Sum([TotwTax]),0)
=IIf(IsNull([TotwTax]),Sum(TotwTax),0)
Click to expand...

Try:

=Nz(Sum([TotwTax]);0)

Or

=IIf(Not IsNumeric(Sum([TotwTax])),0,Sum(TotwTax))

Or

=IIf(IsNull(Sum([TotwTax])),0,Sum(TotwTax))


Acki
 
Ofer said:
Try this

=nz(Sum([TotwTax]),0)

:

I have a Main Form with a Subform. In the footer of the subform I have
an Expression that works as long as there is a record. If there are no
records I get nothing, I want the total to be 0, I tried the two
following expressions, they only work if there are records. Otherwise I
see nothing. Does anyone know what I'm doing wrong?


=Nz([TotwTax],Sum([TotwTax]),0)
=IIf(IsNull([TotwTax]),Sum(TotwTax),0)

Thanks
DS
Click to expand...
Click to expand...
Tried it and it doesn't work.
Thanks
DS
 
Joerg said:
DS said:
I have a Main Form with a Subform. In the footer of the subform I
have an Expression that works as long as there is a record. If there
are no records I get nothing, I want the total to be 0, I tried the
two following expressions, they only work if there are records.
Otherwise I see nothing. Does anyone know what I'm doing wrong?

=Nz([TotwTax],Sum([TotwTax]),0)
=IIf(IsNull([TotwTax]),Sum(TotwTax),0)
Click to expand...


Try:

=Nz(Sum([TotwTax]);0)

Or

=IIf(Not IsNumeric(Sum([TotwTax])),0,Sum(TotwTax))

Or

=IIf(IsNull(Sum([TotwTax])),0,Sum(TotwTax))


Acki
Click to expand...
I don't understand this! None of them work! It shoudn't be this hard.
I've done this before!
Thanks
DS
 
tina said:
try

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

hth


I have a Main Form with a Subform. In the footer of the subform I have
an Expression that works as long as there is a record. If there are no
records I get nothing, I want the total to be 0, I tried the two
following expressions, they only work if there are records. Otherwise I
see nothing. Does anyone know what I'm doing wrong?


=Nz([TotwTax],Sum([TotwTax]),0)
=IIf(IsNull([TotwTax]),Sum(TotwTax),0)

Thanks
DS
Click to expand...
Click to expand...
Thanks Tina, Didn't work.
DS
 
Ofer said:
Try this

=nz(Sum([TotwTax]),0)

:

I have a Main Form with a Subform. In the footer of the subform I have
an Expression that works as long as there is a record. If there are no
records I get nothing, I want the total to be 0, I tried the two
following expressions, they only work if there are records. Otherwise I
see nothing. Does anyone know what I'm doing wrong?


=Nz([TotwTax],Sum([TotwTax]),0)
=IIf(IsNull([TotwTax]),Sum(TotwTax),0)

Thanks
DS
Click to expand...
Click to expand...
I think he problem is with the Query itself. When I go to the Query,
and have no records, the Query shows nothing. The Queries that work on
the subForm show a record line even if there are no records. How can
this be corrected.
Thanks
DS
 
Try and change the UniqueRecords property of the query to true

DS said:
Ofer said:
Try this

=nz(Sum([TotwTax]),0)

:

I have a Main Form with a Subform. In the footer of the subform I have
an Expression that works as long as there is a record. If there are no
records I get nothing, I want the total to be 0, I tried the two
following expressions, they only work if there are records. Otherwise I
see nothing. Does anyone know what I'm doing wrong?


=Nz([TotwTax],Sum([TotwTax]),0)
=IIf(IsNull([TotwTax]),Sum(TotwTax),0)

Thanks
DS
Click to expand...
Click to expand...
I think he problem is with the Query itself. When I go to the Query,
and have no records, the Query shows nothing. The Queries that work on
the subForm show a record line even if there are no records. How can
this be corrected.
Thanks
DS
Click to expand...
 
I think he problem is with the Query itself. When I go to the Query,
and have no records, the Query shows nothing. The Queries that work on
the subForm show a record line even if there are no records. How can
this be corrected.
Thanks
DS
Click to expand...

well, the query itself *may not* be wrong. it sounds like you are working
with a "non-updateable" query. if it's written correctly, and is returning
the dataset you need, and if you do *not* need to be able to add/edit/delete
records in the dataset, then the query is fine.

forcing the unbound control in the subform to show a zero (0) when the query
does not return any records - that just takes a little more work. try this:

in the form's design view, go to the unbound control (i'll call it
txtTotal), and delete the expression from the ControlSource property,
leaving that line in the Properties box empty or blank.

add the following procedure to the form's Open event, as

Private Sub Form_Open(Cancel As Integer)

If Me.Recordset.RecordCount = 0 Then
Me!txtTotals = 0
Else
Me!txtTotals.ControlSource = "=Nz(Sum([TotwTax]),0)"
End If

End Sub

just change the name "txtTotals" to the name of your unbound control. if you
don't know how to add a VBA procedure to a form, post back and i'll give you
instructions - it's not hard to do.

hth
 
tina said:
I think he problem is with the Query itself. When I go to the Query,
and have no records, the Query shows nothing. The Queries that work on
the subForm show a record line even if there are no records. How can
this be corrected.
Thanks
DS
Click to expand...


well, the query itself *may not* be wrong. it sounds like you are working
with a "non-updateable" query. if it's written correctly, and is returning
the dataset you need, and if you do *not* need to be able to add/edit/delete
records in the dataset, then the query is fine.

forcing the unbound control in the subform to show a zero (0) when the query
does not return any records - that just takes a little more work. try this:

in the form's design view, go to the unbound control (i'll call it
txtTotal), and delete the expression from the ControlSource property,
leaving that line in the Properties box empty or blank.

add the following procedure to the form's Open event, as

Private Sub Form_Open(Cancel As Integer)

If Me.Recordset.RecordCount = 0 Then
Me!txtTotals = 0
Else
Me!txtTotals.ControlSource = "=Nz(Sum([TotwTax]),0)"
End If

End Sub

just change the name "txtTotals" to the name of your unbound control. if you
don't know how to add a VBA procedure to a form, post back and i'll give you
instructions - it's not hard to do.

hth
Click to expand...
Tina,
That was pretty slick! It worked great!
Thanks
DS
 
Ofer said:
Try and change the UniqueRecords property of the query to true

:

Ofer wrote:

Try this

=nz(Sum([TotwTax]),0)

:



I have a Main Form with a Subform. In the footer of the subform I have
an Expression that works as long as there is a record. If there are no
records I get nothing, I want the total to be 0, I tried the two
following expressions, they only work if there are records. Otherwise I
see nothing. Does anyone know what I'm doing wrong?


=Nz([TotwTax],Sum([TotwTax]),0)
=IIf(IsNull([TotwTax]),Sum(TotwTax),0)

Thanks
DS
Click to expand...

I think he problem is with the Query itself. When I go to the Query,
and have no records, the Query shows nothing. The Queries that work on
the subForm show a record line even if there are no records. How can
this be corrected.
Thanks
DS
Click to expand...
Click to expand...
That didn't work. I ended up using Tinas suggestion. Thank you for
your help! As always, much appreciated.
DS
 
Back
Top