Trapping empty or null value

[Robert]

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

 

[Allen Browne]

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

 

[Jeanette Cunningham]

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

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

Reply to group, rather than allenbrowne at mvps dot org.

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?

 

[Allen Browne]

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

 

[Jeanette Cunningham]

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

Jeanette Cunningham

 

[John W. Vinson]

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]

 

[Robert]

Thanks. Where do I put this public function?

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

Reply to group, rather than allenbrowne at mvps dot org.

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?

 

[Allen Browne]

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

Reply to group, rather than allenbrowne at mvps dot org.

Thanks. Where do I put this public function?

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

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]

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.

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

Reply to group, rather than allenbrowne at mvps dot org.

Thanks. Where do I put this public function?

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

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?

 

[Allen Browne]

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

Reply to group, rather than allenbrowne at mvps dot org.

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.

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

Reply to group, rather than allenbrowne at mvps dot org.

Thanks. Where do I put this public function?

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

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]

No, the textbox in the footer of the subform does not contain #Error, even
when the subform contains no data and Sum({Amount}) is the control source.
(The field on the main form does contain #Error.)

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

Reply to group, rather than allenbrowne at mvps dot org.

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.

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

Reply to group, rather than allenbrowne at mvps dot org.

Thanks. Where do I put this public function?

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

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\0\0\0>>>>>> 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]

I apologize for mis-stating it earlier.

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

Reply to group, rather than allenbrowne at mvps dot org.

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.

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

Reply to group, rather than allenbrowne at mvps dot org.

Thanks. Where do I put this public function?

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

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\0\0\0>>>>>> 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?

 

[Allen Browne]

Okay: if the subform does not go blank (it shows the new record), and you
can verify that the text box in the subform footer does not contain #Error
(perhaps by switching to Continuous view temporarily), the problem must be
that the way you are referring to it from the main form is wrong.

The reference should be:
=[Sub1].[Form].[Text99]
where Sub1 represents the name of your subform *control*, and Text99
represents the name of the text box in the subform footer that shows the
correct total.

Note that the name of the subform control is not necessarily the same as the
name of the form it contains. That is the subform control's name does not
have to be the same as its SourceObject.

 

[Robert]

This is exactly what I have in the control source of the textbox on the main
form:

=nz([tblrentalchargessubformforreturns].[Form].[txtchargestotal],0)

tblrentalchargessubformforreturns is the subform. txtchargestotal is the
textbox in the subform's footer which, with no data, is blank. But I still
get #Error in txtChargestotal on the main form. (Same name is on both
controls.) What am I doing wrong?

Okay: if the subform does not go blank (it shows the new record), and you
can verify that the text box in the subform footer does not contain #Error
(perhaps by switching to Continuous view temporarily), the problem must be
that the way you are referring to it from the main form is wrong.

The reference should be:
=[Sub1].[Form].[Text99]
where Sub1 represents the name of your subform *control*, and Text99
represents the name of the text box in the subform footer that shows the
correct total.

Note that the name of the subform control is not necessarily the same as
the name of the form it contains. That is the subform control's name does
not have to be the same as its SourceObject.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

No, the textbox in the footer of the subform does not contain #Error,
even when the subform contains no data and Sum({Amount}) is the control
source. (The field on the main form does contain #Error.)

 

[Dirk Goldgar]

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.

I'm pretty sure this link has the answer to youir problem:

http://www.mvps.org/access/forms/frm0022.htm
Forms: #Error when the Subform has no records

It contains a handy little function called "nnz", written by Keri Hardwick,
which you would save in a standard module nd then use in the controlsource
of your main form's text box, like this:

=nnz([subformamounts]![txtamountstotal])

 

[Robert]

Do you know if it works in Access 2007.

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.

I'm pretty sure this link has the answer to youir problem:

http://www.mvps.org/access/forms/frm0022.htm
Forms: #Error when the Subform has no records

It contains a handy little function called "nnz", written by Keri
Hardwick, which you would save in a standard module nd then use in the
controlsource of your main form's text box, like this:

=nnz([subformamounts]![txtamountstotal])


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Do you know if it works in Access 2007.

I don't know; I haven't tried it. I don't see why it wouldn't, though,
provided of course that VBA code is being allowed to run. Why don't you try
it and see?

 

[Allen Browne]

While the form is open, open the Immediate Window (Ctrl+G).
enter:
? Forms![YourMainFormNameHere].Name
substituting your form name.

Once that works, try:
? Forms![YourMainFormNameHere]![tblrentalchargessubformforreturns].Name
If that fails, the name of the subform control is wrong.

If it works, try
?
Forms![YourMainFormNameHere]![tblrentalchargessubformforreturns].Form!txtchargesTotal

Once you have the expression right, transfer it into the control source on
your main form.

 

[Robert]

I'm not following you. If it works try ... . You mean if it doesn't work ?

I used the function in Dirk Goldgar's link and that works in Access 2002.
Haven't tried it in 2007 yet.

While the form is open, open the Immediate Window (Ctrl+G).
enter:
? Forms![YourMainFormNameHere].Name
substituting your form name.

Once that works, try:
? Forms![YourMainFormNameHere]![tblrentalchargessubformforreturns].Name
If that fails, the name of the subform control is wrong.

If it works, try
?
Forms![YourMainFormNameHere]![tblrentalchargessubformforreturns].Form!txtchargesTotal

Once you have the expression right, transfer it into the control source on
your main form.


--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

This is exactly what I have in the control source of the textbox on the
main form:

=nz([tblrentalchargessubformforreturns].[Form].[txtchargestotal],0)

tblrentalchargessubformforreturns is the subform. txtchargestotal is the
textbox in the subform's footer which, with no data, is blank. But I
still get #Error in txtChargestotal on the main form. (Same name is on
both controls.) What am I doing wrong?

 

[Allen Browne]

I'm not following you. If it works try ... . You mean if it doesn't work
?

I used the function in Dirk Goldgar's link and that works in Access 2002.
Haven't tried it in 2007 yet.

Okay, you're there.

The suggestion was to break it down into mouthfuls, so you can pin down
where you had the name wrong.