#Error > Form > Text Box

[Guest]

I've struggled with this before but I don't remember how I fixed it. I have a
text box that points to a field in a subform.

There is probably a better way to do this, but I've had it! I spent 1 1/2
day's trying to display four hardly important figures > but I don't like to
give up...

So after much frustration, it's time to ask for help.

This is what's in the text box: =[Loaned Equipment].[Form]![txtEquipmentValue]
When there is no value, it displays #Error. I've tried,
IsNull([txtEquipValue]), and that doesn't work. I even tried,
Not(IsNull([txtEquipValue])) or whatever the correct syntax is - and that
actually changed the color of the field! This tells me, the #Error is not a
Null Value???

I could really use some help.

The field it points to is this: =nz(Sum([COUNCS]),0)
Does the nz change it from a null value to something different?

Thx!

 

[Marshall Barton]

I've struggled with this before but I don't remember how I fixed it. I have a
text box that points to a field in a subform.

There is probably a better way to do this, but I've had it! I spent 1 1/2
day's trying to display four hardly important figures > but I don't like to
give up...

So after much frustration, it's time to ask for help.

This is what's in the text box: =[Loaned Equipment].[Form]![txtEquipmentValue]
When there is no value, it displays #Error. I've tried,
IsNull([txtEquipValue]), and that doesn't work. I even tried,
Not(IsNull([txtEquipValue])) or whatever the correct syntax is - and that
actually changed the color of the field! This tells me, the #Error is not a
Null Value???

The problem is because the subform has no data, there is no
value, not even Null, for the text box you are referencing.

You can use this kind of expression to check for the no data
situation:

=IIf([Loaned Equipment].[Form].Recordset.RecordCount > 0,
[Loaned Equipment].[Form]![txtEquipmentValue], 0)

Or if you want to ignore all possible reasons (not a good
idea), an alternative would be:

=IIf(IsError([Loaned Equipment].[Form].[txtEquipmentValue]),
0, [Loaned Equipment].[Form]![txtEquipmentValue])

 

[JK]

Thx for the reply, I'll try it as soon as I get to work on Monday. I have
another quick question; In this example, I'm pointing the txt boxes on the
main form (there are three of them in total) to two different subforms on the
main form. What would be considered "good database design," pointing the txt
boxes to the subforms or should I create a new subform to display the
information? Either way, I'll have to use your suggestion - I know. But, I'm
wondering if I'm going about this the wrong way (or does it even matter?).

Thx again for your help!

I've struggled with this before but I don't remember how I fixed it. I have a
text box that points to a field in a subform.

There is probably a better way to do this, but I've had it! I spent 1 1/2
day's trying to display four hardly important figures > but I don't like to
give up...

So after much frustration, it's time to ask for help.

This is what's in the text box: =[Loaned Equipment].[Form]![txtEquipmentValue]
When there is no value, it displays #Error. I've tried,
IsNull([txtEquipValue]), and that doesn't work. I even tried,
Not(IsNull([txtEquipValue])) or whatever the correct syntax is - and that
actually changed the color of the field! This tells me, the #Error is not a
Null Value???

The problem is because the subform has no data, there is no
value, not even Null, for the text box you are referencing.

You can use this kind of expression to check for the no data
situation:

=IIf([Loaned Equipment].[Form].Recordset.RecordCount > 0,
[Loaned Equipment].[Form]![txtEquipmentValue], 0)

Or if you want to ignore all possible reasons (not a good
idea), an alternative would be:

=IIf(IsError([Loaned Equipment].[Form].[txtEquipmentValue]),
0, [Loaned Equipment].[Form]![txtEquipmentValue])

 

[Marshall Barton]

If you can get away with it, the cleanest thing is to
display the total in the subform's header or footer and
leave the main form out of it.

If that is not acceptable (often the case), then using main
form text boxes is the next cleanest way. Using an extra
subform can be made to work, but the added complexity is
rarely worth the effort.
--
Marsh
MVP [MS Access]

Thx for the reply, I'll try it as soon as I get to work on Monday. I have
another quick question; In this example, I'm pointing the txt boxes on the
main form (there are three of them in total) to two different subforms on the
main form. What would be considered "good database design," pointing the txt
boxes to the subforms or should I create a new subform to display the
information? Either way, I'll have to use your suggestion - I know. But, I'm
wondering if I'm going about this the wrong way (or does it even matter?).

I've struggled with this before but I don't remember how I fixed it. I have a
text box that points to a field in a subform.

There is probably a better way to do this, but I've had it! I spent 1 1/2
day's trying to display four hardly important figures > but I don't like to
give up...

So after much frustration, it's time to ask for help.

This is what's in the text box: =[Loaned Equipment].[Form]![txtEquipmentValue]
When there is no value, it displays #Error. I've tried,
IsNull([txtEquipValue]), and that doesn't work. I even tried,
Not(IsNull([txtEquipValue])) or whatever the correct syntax is - and that
actually changed the color of the field! This tells me, the #Error is not a
Null Value???

The problem is because the subform has no data, there is no
value, not even Null, for the text box you are referencing.

You can use this kind of expression to check for the no data
situation:

=IIf([Loaned Equipment].[Form].Recordset.RecordCount > 0,
[Loaned Equipment].[Form]![txtEquipmentValue], 0)

Or if you want to ignore all possible reasons (not a good
idea), an alternative would be:

=IIf(IsError([Loaned Equipment].[Form].[txtEquipmentValue]),
0, [Loaned Equipment].[Form]![txtEquipmentValue])