No Data return 0 instead of #error#

[GL]

Hello

I have a textbox on a report that I want to take the value from a
subreport’s textbox named ‘SumofSharedCost’ . Subreport name is
‘SubShareCost’.

In case the subreport has no data I want the textbox to return 0 instead of
#error#, so I tried to put as control source the phrase seeing below:

=IIF([SubShareCost].[Report].HasData,
[SubShareCost].[Report]![SumofSharedCost] , 0)

but I get the error message
“You omitted an operand or operator, you entered an invalid character…â€
Can you see where the problem is?

Thank you
GL

 

[Todd Lemen]

Hello, GL.
In situations like this, I've always used the "Null Zero" function to
provide data, rather that tripping over an error that occurs when VBA has to
evaluate an expression that may result in null. How about something like:
[SubShareCost].[Report]![SumofSharedCost] = Nz([SubShareCost],0)
In this expression, VBA knows what to do if it encounters a Null in
[SubShareCost], namely, return 0; rather than asking VBA to evaluate
[SubShareCost] and not knowing what to do if it encounters Null...

 

[GL]

you mean to set
=NZ([subShareCost].Report![SumofShareCost, 0)
as control source of the texbox?

Hello, GL.
In situations like this, I've always used the "Null Zero" function to
provide data, rather that tripping over an error that occurs when VBA has to
evaluate an expression that may result in null. How about something like:
[SubShareCost].[Report]![SumofSharedCost] = Nz([SubShareCost],0)
In this expression, VBA knows what to do if it encounters a Null in
[SubShareCost], namely, return 0; rather than asking VBA to evaluate
[SubShareCost] and not knowing what to do if it encounters Null...

Hello

I have a textbox on a report that I want to take the value from a
subreport’s textbox named ‘SumofSharedCost’ . Subreport name is
‘SubShareCost’.

In case the subreport has no data I want the textbox to return 0 instead of
#error#, so I tried to put as control source the phrase seeing below:

=IIF([SubShareCost].[Report].HasData,
[SubShareCost].[Report]![SumofSharedCost] , 0)

but I get the error message
“You omitted an operand or operator, you entered an invalid character…â€
Can you see where the problem is?

Thank you
GL

 

[Duane Hookom]

This expression looks like it should work as long as your subreport control
name is SubShareCost. The control name is not always the name of the
subreport.

Also, I noticed the IIF is in all caps. This suggests you didn't copy and
paste the expression into your posting since I think IIF would change to
IIf.

 

[Todd Lemen]

Now that I look at this, you'll need another piece of the puzzle... We need
to manage a rather specific error in a rather specific way...

Permit me to clarify. As an example: Within the detail section of a report,
there is an unbound text box (let's call it "Text5") whose value we want to
equal a summary field in a subreport. The subreport is titled
"SubShareCost". This subreport carries a summary text box titled
"SumofSharedCost". We want Text5 to equal SumofSharedCost.

So, here's one approach. I'm going to keep Text5 unbound. In the Format
event of the detail section of the report, I'm going to place the following
Event Procedure:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
On Error GoTo SetText5
Dim curTotal as Currency 'Assuming I want Text5 to display as
currency...

'If there are no records in the subreport, then the Summary field is
undefined, and the next line throws an error.
curTotal = Me.SubShareCost.Report!SumofSharedCost

'Otherwise, set Text5 equal to the variable
Me.Text5 = curTotal
Exit Sub


SetText5:
'Error 2427 is thrown when attempting to reference the summary field with no
value
If Err.Number = 2427 Then
Me.Text5 = 0
Else 'If some other error occurred, report the error...
MsgBox Err.Number & ", " & Err.Description
End If
End Sub

Hope this helps...

TL

you mean to set
=NZ([subShareCost].Report![SumofShareCost, 0)
as control source of the texbox?

Hello, GL.
In situations like this, I've always used the "Null Zero" function to
provide data, rather that tripping over an error that occurs when VBA has to
evaluate an expression that may result in null. How about something like:
[SubShareCost].[Report]![SumofSharedCost] = Nz([SubShareCost],0)
In this expression, VBA knows what to do if it encounters a Null in
[SubShareCost], namely, return 0; rather than asking VBA to evaluate
[SubShareCost] and not knowing what to do if it encounters Null...

Hello

I have a textbox on a report that I want to take the value from a
subreport’s textbox named ‘SumofSharedCost’ . Subreport name is
‘SubShareCost’.

In case the subreport has no data I want the textbox to return 0 instead of
#error#, so I tried to put as control source the phrase seeing below:

=IIF([SubShareCost].[Report].HasData,
[SubShareCost].[Report]![SumofSharedCost] , 0)

but I get the error message
“You omitted an operand or operator, you entered an invalid character…â€
Can you see where the problem is?

Thank you
GL

 

[Duane Hookom]

I think this is all totally unnecessary. You shouldn't need code and I doubt
the Nz() function would ever work if the subreport doesn't return any
records.

The original expression should have worked if it was typed in with accurate
control names.

--
Duane Hookom
MS Access MVP
--

Now that I look at this, you'll need another piece of the puzzle... We
need
to manage a rather specific error in a rather specific way...

Permit me to clarify. As an example: Within the detail section of a
report,
there is an unbound text box (let's call it "Text5") whose value we want
to
equal a summary field in a subreport. The subreport is titled
"SubShareCost". This subreport carries a summary text box titled
"SumofSharedCost". We want Text5 to equal SumofSharedCost.

So, here's one approach. I'm going to keep Text5 unbound. In the Format
event of the detail section of the report, I'm going to place the
following
Event Procedure:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
On Error GoTo SetText5
Dim curTotal as Currency 'Assuming I want Text5 to display as
currency...

'If there are no records in the subreport, then the Summary field is
undefined, and the next line throws an error.
curTotal = Me.SubShareCost.Report!SumofSharedCost

'Otherwise, set Text5 equal to the variable
Me.Text5 = curTotal
Exit Sub


SetText5:
'Error 2427 is thrown when attempting to reference the summary field with
no
value
If Err.Number = 2427 Then
Me.Text5 = 0
Else 'If some other error occurred, report the error...
MsgBox Err.Number & ", " & Err.Description
End If
End Sub

Hope this helps...

TL

you mean to set
=NZ([subShareCost].Report![SumofShareCost, 0)
as control source of the texbox?

Hello, GL.
In situations like this, I've always used the "Null Zero" function to
provide data, rather that tripping over an error that occurs when VBA
has to
evaluate an expression that may result in null. How about something
like:
[SubShareCost].[Report]![SumofSharedCost] = Nz([SubShareCost],0)
In this expression, VBA knows what to do if it encounters a Null in
[SubShareCost], namely, return 0; rather than asking VBA to evaluate
[SubShareCost] and not knowing what to do if it encounters Null...

:

Hello

I have a textbox on a report that I want to take the value from a
subreport's textbox named 'SumofSharedCost' . Subreport name is
'SubShareCost'.

In case the subreport has no data I want the textbox to return 0
instead of
#error#, so I tried to put as control source the phrase seeing below:

=IIF([SubShareCost].[Report].HasData,
[SubShareCost].[Report]![SumofSharedCost] , 0)

but I get the error message
"You omitted an operand or operator, you entered an invalid
character."
Can you see where the problem is?

Thank you
GL

 

[GL]

It was one of the few unexplained erratic behavior situations I have faced
while working with Access.
I deleted the textbox put a new one, paste in it exactly the same sentence
and magic, it worked!

Thank you for your help
GL

This expression looks like it should work as long as your subreport control
name is SubShareCost. The control name is not always the name of the
subreport.

Also, I noticed the IIF is in all caps. This suggests you didn't copy and
paste the expression into your posting since I think IIF would change to
IIf.

--
Duane Hookom
MS Access MVP
--

Hello

I have a textbox on a report that I want to take the value from a
subreport's textbox named 'SumofSharedCost' . Subreport name is
'SubShareCost'.

In case the subreport has no data I want the textbox to return 0 instead
of
#error#, so I tried to put as control source the phrase seeing below:

=IIF([SubShareCost].[Report].HasData,
[SubShareCost].[Report]![SumofSharedCost] , 0)

but I get the error message
"You omitted an operand or operator, you entered an invalid character."
Can you see where the problem is?

Thank you
GL