Null Values, #Error and Forms

[Guest]

I want to display the number of records in a query where I set the field
=[Form}.[RecordsetClone].[RecordCount] in the ControlSource property. It
works fine except that when there are no records found, I get an "#Error"
message. I tried an IIF clause to set it to '0' if it were null, but
couldn't get it to work. Any suggestions on how to get it to display a zero
would be greatly appreciated. Thanks in advance.

 

[ruralguy via AccessMonster.com]

Have you looked at the IsError() function?

I want to display the number of records in a query where I set the field
=[Form}.[RecordsetClone].[RecordCount] in the ControlSource property. It
works fine except that when there are no records found, I get an "#Error"
message. I tried an IIF clause to set it to '0' if it were null, but
couldn't get it to work. Any suggestions on how to get it to display a zero
would be greatly appreciated. Thanks in advance.

 

[Guest]

Works fine for me. i am using A2003.

I added an unbound textbox to the header of a form with a query for the
record source of the form.

In the control source of the textbox I entered:

=[Form].[RecordsetClone].[RecordCount]


It displays 0 (zero) when there are no records and the number of record
otherwise.

AFAIK, the recordcount should never be Null. Null would mean that the count
is unknown. If there are no records in a table or query, the recordcount
should be zero.

Maybe you could tell us a little more about what you are trying to do? What
you have tried; post the SQL of the query for the form...


HTH

 

[Guest]

Since submitting my post, I noticed that the problem is with my query. I
have two queries, say Query1 and Query2, where I list the returned records in
their respective subform. I noticed that Query 1 indicates where I can input
a new, blank record, i.e., it shows (auto number) in the first field at the
end of the listed records. (Sorry for my ignorance on the proper
terminology.) If there are no records returned, the query/subform shows only
a new, blank record (auto number info) and the record count does show as 0.
So it is okay.

Query2, however, does not have the extra, blank record at the end. So the
subform just shows the field names but no new or blank record. For this
case, the count show #Error.

The problem may be in the way I've constructed the relationships between the
tables that are used in the queries. Both have different types of
relationships. In both, I set the Allow Additions (I think that's the right
one) property to Yes. So, I'm not sure how I will correct the problem. Oh,
well.

Works fine for me. i am using A2003.

I added an unbound textbox to the header of a form with a query for the
record source of the form.

In the control source of the textbox I entered:

=[Form].[RecordsetClone].[RecordCount]


It displays 0 (zero) when there are no records and the number of record
otherwise.

AFAIK, the recordcount should never be Null. Null would mean that the count
is unknown. If there are no records in a table or query, the recordcount
should be zero.

Maybe you could tell us a little more about what you are trying to do? What
you have tried; post the SQL of the query for the form...


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

I want to display the number of records in a query where I set the field
=[Form}.[RecordsetClone].[RecordCount] in the ControlSource property. It
works fine except that when there are no records found, I get an "#Error"
message. I tried an IIF clause to set it to '0' if it were null, but
couldn't get it to work. Any suggestions on how to get it to display a zero
would be greatly appreciated. Thanks in advance.

 

[Guest]

I was able to workaround my problem using a combination of the IIF and
IsError functions. Thanks for the suggestion, ruralguy!

Have you looked at the IsError() function?

I want to display the number of records in a query where I set the field
=[Form}.[RecordsetClone].[RecordCount] in the ControlSource property. It
works fine except that when there are no records found, I get an "#Error"
message. I tried an IIF clause to set it to '0' if it were null, but
couldn't get it to work. Any suggestions on how to get it to display a zero
would be greatly appreciated. Thanks in advance.