By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,603 Members | 1,093 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,603 IT Pros & Developers. It's quick & easy.

IsNull

P: n/a
I receive the foolwoing error in my report :
"You entered an expression that has no value"

In my report i am summing up the all orders with the control
=Sum([liters])
This control is called Bonus. I have then another control called
GrandTotal that calculates the bonus like that:

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
Me![bonus] = Me![Sum] * rate
Me![TotalBonus] = Me![GrandTotal] * 5/100
End Sub

The report opens ok in case the bonus is not null.Otherwise i receive
the above message. I have tried the following code but with no suucess:

Private Sub Report_Open(Cancel As Integer)
If IsNull(Me![bonus]) Then
MsgBox " There are no orders ", vbExclamation
Me![bonus].Value = 0
Exit Sub
End If
End Sub

I cannot see why the IsNull command is not working.
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies

P: n/a
Use the Nz() function

=Sum(Nz([Liters],0)
or
=Sum(Nz([Liters],"")
or

If Nz(Me!Bonus,"") = "" Then ...
--

Danny J. Lesandrini
dl*********@hotmail.com
http://amazecreations.com/datafast
"Markus" <ge********@yahoo.com> wrote ...
I receive the foolwoing error in my report :
"You entered an expression that has no value"

In my report i am summing up the all orders with the control
=Sum([liters])
This control is called Bonus. I have then another control called
GrandTotal that calculates the bonus like that:

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
Me![bonus] = Me![Sum] * rate
Me![TotalBonus] = Me![GrandTotal] * 5/100
End Sub

The report opens ok in case the bonus is not null.Otherwise i receive
the above message. I have tried the following code but with no suucess:

Private Sub Report_Open(Cancel As Integer)
If IsNull(Me![bonus]) Then
MsgBox " There are no orders ", vbExclamation
Me![bonus].Value = 0
Exit Sub
End If
End Sub

I cannot see why the IsNull command is not working.

Nov 12 '05 #2

P: n/a
Marcus, if there are no records in the report, the text box will have no
value. Attempting to sum or perform math on the non-existent value results
in Error (not Null).

Test the report's HasData property
Alternatively, IsError() with IIf() may help.
Or you may want to cancel the report's NoData event.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Markus" <ge********@yahoo.com> wrote in message
news:ea**************************@posting.google.c om...
I receive the foolwoing error in my report :
"You entered an expression that has no value"

In my report i am summing up the all orders with the control
=Sum([liters])
This control is called Bonus. I have then another control called
GrandTotal that calculates the bonus like that:

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
Me![bonus] = Me![Sum] * rate
Me![TotalBonus] = Me![GrandTotal] * 5/100
End Sub

The report opens ok in case the bonus is not null.Otherwise i receive
the above message. I have tried the following code but with no suucess:

Private Sub Report_Open(Cancel As Integer)
If IsNull(Me![bonus]) Then
MsgBox " There are no orders ", vbExclamation
Me![bonus].Value = 0
Exit Sub
End If
End Sub

I cannot see why the IsNull command is not working.

Nov 12 '05 #3

P: n/a
On 24 Dec 2003 04:41:29 -0800, ge********@yahoo.com (Markus) wrote:

Perhaps you should ensure that the underlying query has no null
values. The Nz function is handy for that.

-Tom.

I receive the foolwoing error in my report :
"You entered an expression that has no value"

In my report i am summing up the all orders with the control
=Sum([liters])
This control is called Bonus. I have then another control called
GrandTotal that calculates the bonus like that:

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
Me![bonus] = Me![Sum] * rate
Me![TotalBonus] = Me![GrandTotal] * 5/100
End Sub

The report opens ok in case the bonus is not null.Otherwise i receive
the above message. I have tried the following code but with no suucess:

Private Sub Report_Open(Cancel As Integer)
If IsNull(Me![bonus]) Then
MsgBox " There are no orders ", vbExclamation
Me![bonus].Value = 0
Exit Sub
End If
End Sub

I cannot see why the IsNull command is not working.


Nov 12 '05 #4

P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Come on, youse guys! Yer ferget'in the NoData event.

If the query that is the report's RecordSource doesn't return any data
the NoData event will fire, if it exists. The OnOpen event occurs
BEFORE the RecordSource retrieves the data - that's why Markus' code
is not working - there isn't any data to work with. Try this as the
NoData event procedure:

Private Sub Report_NoData(Cancel As Integer)

MsgBox "No data fits the criteria of this report", vbCritical
Cancel = True

End Sub

HTH,

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP+t6RIechKqOuFEgEQIArQCaAtoSKBSA0wQ2QWU+Weo2Ca bb0z4AoMsc
oMmAVqPg+H/9w1LdtOoLUA+0
=MxGy
-----END PGP SIGNATURE-----
Markus wrote:
I receive the foolwoing error in my report :
"You entered an expression that has no value"

In my report i am summing up the all orders with the control
=Sum([liters])
This control is called Bonus. I have then another control called
GrandTotal that calculates the bonus like that:

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
Me![bonus] = Me![Sum] * rate
Me![TotalBonus] = Me![GrandTotal] * 5/100
End Sub

The report opens ok in case the bonus is not null.Otherwise i receive
the above message. I have tried the following code but with no suucess:

Private Sub Report_Open(Cancel As Integer)
If IsNull(Me![bonus]) Then
MsgBox " There are no orders ", vbExclamation
Me![bonus].Value = 0
Exit Sub
End If
End Sub

I cannot see why the IsNull command is not working.


Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.