Trapping empty or null value

I have a subform with a textbox txtAmount bound to field Amount. On the
subform's footer is txtTotal with control source =Sum([amount]).

When there is no data in the subform I see #Error in the txtTotal. How can
I change

=Sum([amount])

to something that will test for this condition and display 0 when there is
no data in the subform instead?.

Robert

Unless you are using Access 2007, you can check the RecordCount of the
RecordsetClone of the subform. Unfortunately, the new version errors with
that, so you have to use a function call.

Public Function FormHasNoRecords(frm As Form) As Boolean
FormHasNoRecords = (frm.Recordset.RecordCount = 0&)
End Function

Then the ControlSource of your text box would look like this:
=IIf(FormHasNoRecords([Form]), 0, Nz(Sum([Amount],0))

--
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.

"Robert" wrote in message
...
I have a subform with a textbox txtAmount bound to field Amount. On the
subform's footer is txtTotal with control source =Sum([amount]).

When there is no data in the subform I see #Error in the txtTotal. How
can I change

=Sum([amount])

to something that will test for this condition and display 0 when there is
no data in the subform instead?

Hi Allen,
could you help with explanation of &
I now and then see the use of & in a function where I would not expect it,
such as
frm.Recordset.RecordCount = 0&

Jeanette Cunningham

"Allen Browne" wrote in message
...
Unless you are using Access 2007, you can check the RecordCount of the
RecordsetClone of the subform. Unfortunately, the new version errors with
that, so you have to use a function call.

Public Function FormHasNoRecords(frm As Form) As Boolean
FormHasNoRecords = (frm.Recordset.RecordCount = 0&)
End Function

Then the ControlSource of your text box would look like this:
=IIf(FormHasNoRecords([Form]), 0, Nz(Sum([Amount],0))

--
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.

"Robert" wrote in message
...
I have a subform with a textbox txtAmount bound to field Amount. On the
subform's footer is txtTotal with control source =Sum([amount]).

When there is no data in the subform I see #Error in the txtTotal. How
can I change

=Sum([amount])

to something that will test for this condition and display 0 when there
is no data in the subform instead?

Okay, Jeanette. In short, it is a type declaration character:
0 is an Integer
0& is a Long Integer

In ancient days, the BASIC language let you to declare the data type of a
variable by adding a suffix to the name of the variable. So:
Dim A$
was equivalent to:
Dim A As String

Just from memory, the suffixes that come to mind we
String $
Integer %
Long &
Currency @
Double #
Single !
These days, no developer worth her salt would use naming conventions like
that, so you never see them in books or code examples. However, they are a
quite neat way of specifying the data type for literal values.

Open the Immediate Window (Ctrl+G), and enter:
? 200 * 200
Overflow error? That's because VBA interprets 200 as in integer, multiplies
the two integers, and cannot handle the integer result, since it is larger
than 32767.

To solve the problem, explicitly typecast to a Long:
? CLng(200) * CLng(200)
VBA now multiplies two longs, and it works without overflow.

You could use the type declaration character:
? 200& * 200&
That's identical to the expression above, but more efficient: the numbers
are created as the correct type, and you avoid 2 function calls.

This gets muddied because VBA does *lots* of typecasting implicitly. For
example:
? 200 * 200&
VBA is asked to multiply an integer and a long. It cannot do that, so it
implicitly typecasts the smaller value into the larger one, performs the
calculation, and returns the value correctly. This implicit typecasting is
what I am trying to avoid.

In my view, a good developer needs to be aware of the data type they are
using, and use it intentionally. The RecordCount of a recordset is a Long.
The code:
If frm.Recordset.RecordCount = 0
works, but only because VBA typecasts the literal integer zero into a Long.
It would be better to supply a long integer in the first place. The type
declaration suffix character provides a way to do that:
If frm.Recordset.RecordCount = 0&

Similarly:
If Len(strOutput) 0& Then
If dblQuantity 0# Then

Interestingly VBA itself substitutes the # if you type:
If dblQuantity 0.0 Then

Too pedantic? Perhaps.
More efficient? Marginally.
Clearer thinking about your data? Unquestionably.
Worth the effort? Your call.

--
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.

"Jeanette Cunningham" wrote in message
...
Hi Allen,
could you help with explanation of &
I now and then see the use of & in a function where I would not expect it,
such as
frm.Recordset.RecordCount = 0&

Jeanette Cunningham

"Allen Browne" wrote in message
...
Unless you are using Access 2007, you can check the RecordCount of the
RecordsetClone of the subform. Unfortunately, the new version errors with
that, so you have to use a function call.

Public Function FormHasNoRecords(frm As Form) As Boolean
FormHasNoRecords = (frm.Recordset.RecordCount = 0&)
End Function

Thanks very much, so much still to learn about using ms access.

Jeanette Cunningham

"Allen Browne" wrote in message
...
Okay, Jeanette. In short, it is a type declaration character:
0 is an Integer
0& is a Long Integer

In ancient days, the BASIC language let you to declare the data type of a
variable by adding a suffix to the name of the variable. So:
Dim A$
was equivalent to:
Dim A As String

Just from memory, the suffixes that come to mind we
String $
Integer %
Long &
Currency @
Double #
Single !
These days, no developer worth her salt would use naming conventions like
that, so you never see them in books or code examples. However, they are a
quite neat way of specifying the data type for literal values.

Open the Immediate Window (Ctrl+G), and enter:
? 200 * 200
Overflow error? That's because VBA interprets 200 as in integer,
multiplies the two integers, and cannot handle the integer result, since
it is larger than 32767.

To solve the problem, explicitly typecast to a Long:
? CLng(200) * CLng(200)
VBA now multiplies two longs, and it works without overflow.

You could use the type declaration character:
? 200& * 200&
That's identical to the expression above, but more efficient: the numbers
are created as the correct type, and you avoid 2 function calls.

This gets muddied because VBA does *lots* of typecasting implicitly. For
example:
? 200 * 200&
VBA is asked to multiply an integer and a long. It cannot do that, so it
implicitly typecasts the smaller value into the larger one, performs the
calculation, and returns the value correctly. This implicit typecasting is
what I am trying to avoid.

In my view, a good developer needs to be aware of the data type they are
using, and use it intentionally. The RecordCount of a recordset is a Long.
The code:
If frm.Recordset.RecordCount = 0
works, but only because VBA typecasts the literal integer zero into a
Long. It would be better to supply a long integer in the first place. The
type declaration suffix character provides a way to do that:
If frm.Recordset.RecordCount = 0&

Similarly:
If Len(strOutput) 0& Then
If dblQuantity 0# Then

Interestingly VBA itself substitutes the # if you type:
If dblQuantity 0.0 Then

Too pedantic? Perhaps.
More efficient? Marginally.
Clearer thinking about your data? Unquestionably.
Worth the effort? Your call.

--
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.

"Jeanette Cunningham" wrote in message
...
Hi Allen,
could you help with explanation of &
I now and then see the use of & in a function where I would not expect
it, such as
frm.Recordset.RecordCount = 0&

Jeanette Cunningham

"Allen Browne" wrote in message
...
Unless you are using Access 2007, you can check the RecordCount of the
RecordsetClone of the subform. Unfortunately, the new version errors
with that, so you have to use a function call.

Public Function FormHasNoRecords(frm As Form) As Boolean
FormHasNoRecords = (frm.Recordset.RecordCount = 0&)
End Function

On Sat, 12 Jan 2008 14:30:23 +0900, "Allen Browne"
wrote:

Too pedantic? Perhaps.
More efficient? Marginally.
Clearer thinking about your data? Unquestionably.
Worth the effort? Your call.

Very educational and useful, even to a supposed "expert" like me? You betcha.

Thanks Allen!

John W. Vinson [MVP]

Thanks. Where do I put this public function?

"Allen Browne" wrote in message
...
Unless you are using Access 2007, you can check the RecordCount of the
RecordsetClone of the subform. Unfortunately, the new version errors with
that, so you have to use a function call.

Public Function FormHasNoRecords(frm As Form) As Boolean
FormHasNoRecords = (frm.Recordset.RecordCount = 0&)
End Function

Then the ControlSource of your text box would look like this:
=IIf(FormHasNoRecords([Form]), 0, Nz(Sum([Amount],0))

--
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.

"Robert" wrote in message
...
I have a subform with a textbox txtAmount bound to field Amount. On the
subform's footer is txtTotal with control source =Sum([amount]).

When there is no data in the subform I see #Error in the txtTotal. How
can I change

=Sum([amount])

to something that will test for this condition and display 0 when there
is no data in the subform instead?

Put the function in a standard module.

Create a new module (Modules tab of Database window.)
Paste the code there.
Save with a name such as Module1.

Looks like this is going to arise often, so have added this to my website:
Avoid #Error in form/report with no records
at:
http://allenbrowne.com/RecordCountError.html

--
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.

"Robert" wrote in message
...
Thanks. Where do I put this public function?

"Allen Browne" wrote in message
...
Unless you are using Access 2007, you can check the RecordCount of the
RecordsetClone of the subform. Unfortunately, the new version errors with
that, so you have to use a function call.

Public Function FormHasNoRecords(frm As Form) As Boolean
FormHasNoRecords = (frm.Recordset.RecordCount = 0&)
End Function

Then the ControlSource of your text box would look like this:
=IIf(FormHasNoRecords([Form]), 0, Nz(Sum([Amount],0))

"Robert" wrote in message
...
I have a subform with a textbox txtAmount bound to field Amount. On the
subform's footer is txtTotal with control source =Sum([amount]).

When there is no data in the subform I see #Error in the txtTotal. How
can I change

=Sum([amount])

to something that will test for this condition and display 0 when there
is no data in the subform instead?

I think I led you down the wrong path. When I look at the subform in
continuous forms format, there is no #Error on the total (which is in the
footer and which cannot be seen in the datasheet verxion.) I get the #Error
in the field on the main form which references it. So I still have the
probleam as

=nz([subformamounts].[Form].[txtamountstotal],0)

returns #Error. I 'm sorry but I forgot I was looking at a reference to the
field, not the field itself. Not sure how this will affect your website
entry.

I need a 0 not #Error for this field if there is no data. It works with
data. I"m using Access 2002. The client wants to use Access 2007.

"Allen Browne" wrote in message
...
Put the function in a standard module.

Create a new module (Modules tab of Database window.)
Paste the code there.
Save with a name such as Module1.

Looks like this is going to arise often, so have added this to my website:
Avoid #Error in form/report with no records
at:
http://allenbrowne.com/RecordCountError.html

--
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.

"Robert" wrote in message
...
Thanks. Where do I put this public function?

"Allen Browne" wrote in message
...
Unless you are using Access 2007, you can check the RecordCount of the
RecordsetClone of the subform. Unfortunately, the new version errors
with that, so you have to use a function call.

Public Function FormHasNoRecords(frm As Form) As Boolean
FormHasNoRecords = (frm.Recordset.RecordCount = 0&)
End Function

Then the ControlSource of your text box would look like this:
=IIf(FormHasNoRecords([Form]), 0, Nz(Sum([Amount],0))

"Robert" wrote in message
...
I have a subform with a textbox txtAmount bound to field Amount. On the
subform's footer is txtTotal with control source =Sum([amount]).

When there is no data in the subform I see #Error in the txtTotal. How
can I change

=Sum([amount])

to something that will test for this condition and display 0 when there
is no data in the subform instead?

Robert, the cause is the same.

Let's verify that:
a) The problem occurs only when the subform has no data, and
b) In that case, the subform goes completely blank.

If so, the text box in the Form Footer of the subform, it contains #Error
when the subform has no data. So, when you refer to that in from the main
form, it (quite correctly) reports that #Error is the outcome.

To fix it, follow the new article.

--
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.

"Robert" wrote in message
...
I think I led you down the wrong path. When I look at the subform in
continuous forms format, there is no #Error on the total (which is in the
footer and which cannot be seen in the datasheet verxion.) I get the
#Error in the field on the main form which references it. So I still have
the probleam as

=nz([subformamounts].[Form].[txtamountstotal],0)

returns #Error. I 'm sorry but I forgot I was looking at a reference to
the field, not the field itself. Not sure how this will affect your
website entry.

I need a 0 not #Error for this field if there is no data. It works with
data. I"m using Access 2002. The client wants to use Access 2007.

"Allen Browne" wrote in message
...
Put the function in a standard module.

Create a new module (Modules tab of Database window.)
Paste the code there.
Save with a name such as Module1.

Looks like this is going to arise often, so have added this to my
website:
Avoid #Error in form/report with no records
at:
http://allenbrowne.com/RecordCountError.html

--
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.

"Robert" wrote in message
...
Thanks. Where do I put this public function?

"Allen Browne" wrote in message
...
Unless you are using Access 2007, you can check the RecordCount
of the RecordsetClone of the subform. Unfortunately, the new version
errors with that, so you have to use a function call.

Public Function FormHasNoRecords(frm As Form) As Boolean
FormHasNoRecords = (frm.Recordset.RecordCount = 0&)
End Function

Then the ControlSource of your text box would look like this:
=IIf(FormHasNoRecords([Form]), 0, Nz(Sum([Amount],0))

"Robert" wrote in message
...
I have a subform with a textbox txtAmount bound to field Amount. On the
subform's footer is txtTotal with control source
=Sum([amount]).
When there is no data in the subform I see #Error in the txtTotal.
How can I change

=Sum([amount])

to something that will test for this condition and display 0 when
there is no data in the subform instead?