Can't use function in a report

[Laurel]

I have a simple report that prints a date and a value (columnname 'bonus')
in its detail section. It seems to work fine. My problem is that if I try to
put the Bonus into a function, I get an error. I've tried the simplest
Access functions, such as =abs(Bonus) and =format([bonus],"##.#"). In those
cases, the report prints
#Error instead of the value of Bonus. If I try to use a function written by
me, (=fncTranslateTokens([bonus]), I get this VB error: "2427 - You have
entered an expression that has no value." And, indeed, if I look at the
parameter in the function via the debugger, it has no value - doesn't show
null or anything.

Here are possibly relevant facts.

=sum([bonus]) in the footer of the report works fine.

I've removed [bonus] from the detail, saved, and then re-added it.

I've "comact and repair"ed the database

Recordsource for the report is a query
There is a filter
But remember that the report works fine - good data - if I just use
[bonus] or bonus

bonus is a datatype: number, field Size: integer

This is the text of the function written by me that I'm trying to use. I've
declared the parameter as a variant, but at first I wasn't declaring it at
all. The control goes to the error handler which prints the message.
Reminder - can't use format(bonus,"##.#") or abs([bonus]) either, so the
problem
isn't limited to this function.

Public Function fncTranslateTokens(adc_Tokens As Variant)

Dim ls_temp As String
Dim lv_temp As Variant

On Error GoTo Err_TranslateTokens

If IsNull(gdc_TokenValue) Or gdc_TokenValue <= 0 Then
MsgBox ("Please enter avalue for the parameter 'TokenValue.'")
fncTranslateTokens = Failure
GoTo Exit_TranslateTokens
End If


fncTranslateTokens = Format(gdc_TokenValue * adc_Tokens, "$#,##0.00")

Exit_TranslateTokens:
Exit Function

Err_TranslateTokens:
If IsNull(adc_Tokens) Then
ls_temp = "Null"
Else
ls_temp = adc_Tokens
End If
ls_temp = "fncTranslateTokens:" & " adc_tokens: " & lv_temp _
& vbCrLf & Err.Description

MsgBox ls_temp
fncTranslateTokens = Failure

Resume Exit_TranslateTokens
End Function

 

[J. Goddard]

It sounds like your "bonus" field contains a Null value. Try the Nz
function to convert Null to 0 whenver you reference it in a function:

abs(Nz([bonus],0))

John

 

[Laurel]

No, it never does. Nulls and zeroes are filtered out, and when the report
prints with just bonus, I see good non-null, non-zero data.
BUT, I went back several generations to a copy of the report that didn't
have the sum([bonus]) in the footer. I was able to put functions in the
detail, no problem. I'm going to try to figure out what, exactly, caused
the report to "go bad." Will report back.

It sounds like your "bonus" field contains a Null value. Try the Nz
function to convert Null to 0 whenver you reference it in a function:

abs(Nz([bonus],0))

John

I have a simple report that prints a date and a value (columnname
'bonus') in its detail section. It seems to work fine. My problem is that
if I try to put the Bonus into a function, I get an error. I've tried
the simplest Access functions, such as =abs(Bonus) and
=format([bonus],"##.#"). In those cases, the report prints
#Error instead of the value of Bonus. If I try to use a function written
by me, (=fncTranslateTokens([bonus]), I get this VB error: "2427 - You
have entered an expression that has no value." And, indeed, if I look at
the parameter in the function via the debugger, it has no value - doesn't
show null or anything.

Here are possibly relevant facts.

=sum([bonus]) in the footer of the report works fine.

I've removed [bonus] from the detail, saved, and then re-added it.

I've "comact and repair"ed the database

Recordsource for the report is a query
There is a filter
But remember that the report works fine - good data - if I just
use
[bonus] or bonus

bonus is a datatype: number, field Size: integer

This is the text of the function written by me that I'm trying to use.
I've
declared the parameter as a variant, but at first I wasn't declaring it
at
all. The control goes to the error handler which prints the message.
Reminder - can't use format(bonus,"##.#") or abs([bonus]) either, so the
problem
isn't limited to this function.

Public Function fncTranslateTokens(adc_Tokens As Variant)

Dim ls_temp As String
Dim lv_temp As Variant

On Error GoTo Err_TranslateTokens

If IsNull(gdc_TokenValue) Or gdc_TokenValue <= 0 Then
MsgBox ("Please enter avalue for the parameter 'TokenValue.'")
fncTranslateTokens = Failure
GoTo Exit_TranslateTokens
End If


fncTranslateTokens = Format(gdc_TokenValue * adc_Tokens, "$#,##0.00")

Exit_TranslateTokens:
Exit Function

Err_TranslateTokens:
If IsNull(adc_Tokens) Then
ls_temp = "Null"
Else
ls_temp = adc_Tokens
End If
ls_temp = "fncTranslateTokens:" & " adc_tokens: " & lv_temp _
& vbCrLf & Err.Description

MsgBox ls_temp
fncTranslateTokens = Failure

Resume Exit_TranslateTokens
End Function

 

[Laurel]

I mentioned in an earlier reply that I was able to get this to work in an
earlier generation of the report. But it turns out that the critical
variable was how I made the change, not which version of the report I used.
If I copied the existing, simple [bonus] item in the detail and then put the
=fncTranslateTokens([bonus]) in that copied item, it worked. It continued
to work if I deleted the original [bonus].

I'm sure this is the thing that made it work, since no other approach would
work in either generation.

I guess I have a subtly corrupt report object. This sort of thing seems to
happen in Access more than other languages I've used. Is it just me and my
inexperience?

It sounds like your "bonus" field contains a Null value. Try the Nz
function to convert Null to 0 whenver you reference it in a function:

abs(Nz([bonus],0))

John

I have a simple report that prints a date and a value (columnname
'bonus') in its detail section. It seems to work fine. My problem is that
if I try to put the Bonus into a function, I get an error. I've tried
the simplest Access functions, such as =abs(Bonus) and
=format([bonus],"##.#"). In those cases, the report prints
#Error instead of the value of Bonus. If I try to use a function written
by me, (=fncTranslateTokens([bonus]), I get this VB error: "2427 - You
have entered an expression that has no value." And, indeed, if I look at
the parameter in the function via the debugger, it has no value - doesn't
show null or anything.

Here are possibly relevant facts.

=sum([bonus]) in the footer of the report works fine.

I've removed [bonus] from the detail, saved, and then re-added it.

I've "comact and repair"ed the database

Recordsource for the report is a query
There is a filter
But remember that the report works fine - good data - if I just
use
[bonus] or bonus

bonus is a datatype: number, field Size: integer

This is the text of the function written by me that I'm trying to use.
I've
declared the parameter as a variant, but at first I wasn't declaring it
at
all. The control goes to the error handler which prints the message.
Reminder - can't use format(bonus,"##.#") or abs([bonus]) either, so the
problem
isn't limited to this function.

Public Function fncTranslateTokens(adc_Tokens As Variant)

Dim ls_temp As String
Dim lv_temp As Variant

On Error GoTo Err_TranslateTokens

If IsNull(gdc_TokenValue) Or gdc_TokenValue <= 0 Then
MsgBox ("Please enter avalue for the parameter 'TokenValue.'")
fncTranslateTokens = Failure
GoTo Exit_TranslateTokens
End If


fncTranslateTokens = Format(gdc_TokenValue * adc_Tokens, "$#,##0.00")

Exit_TranslateTokens:
Exit Function

Err_TranslateTokens:
If IsNull(adc_Tokens) Then
ls_temp = "Null"
Else
ls_temp = adc_Tokens
End If
ls_temp = "fncTranslateTokens:" & " adc_tokens: " & lv_temp _
& vbCrLf & Err.Description

MsgBox ls_temp
fncTranslateTokens = Failure

Resume Exit_TranslateTokens
End Function

 

[Albert D. Kallal]

I've tried the simplest Access functions, such as =abs(Bonus) and

=format([bonus],"##.#"). In those cases, the report prints

Where/how are you calling the functions? Don't forget that if you are going
to place a expression/function in the datasouce setting of a text box on a
form, the control name SHOULD NOT conflict with an existing field name that
is behind the report data source (ie: the reports underlying reocrdset).

For the most part, most developers do allow the text box control on a report
to be the same name as the field name (this is the default). This works when
you need to display a field from the underlying recordsource of the report.
However, if you name a text box the same as a field, and then start typing
in expressions into the data source of this text box, then things get real
messed up real fast. So, keep this in mind.

You will need to use a text name, not the name of the actually data field to
reference the value in a report. Unlike forms in which your code (and text
boxes) can reference any underlying field (even without a text box control).
However, in a report, this is NOT the case, and you need to use a text box
to reference the data.

=sum([bonus]) in the footer of the report works fine.

Sure, ok...but, what is the name of the text box control in above?

Further, you do realize that event code in a report can only access the text
box controls in that event?

(eg: code run in the format event for the reports heading can only
use/reference text box controls in the reports header). (note I
said code, since expressions in text boxes do NOT have this limitation).

This is the text of the function written by me that I'm trying to use.

How are you (where) are you using the expression?

fncTranslateTokens = Format(gdc_TokenValue * adc_Tokens, "$#,##0.00")

I don't see where you set the value of gdc_TokenValue ?

(perhaps it is global??, but do remember that any-unhandled error will reset
all variables).

 

[Laurel]

I've tried the simplest Access functions, such as =abs(Bonus) and
=format([bonus],"##.#"). In those cases, the report prints

Where/how are you calling the functions? Don't forget that if you are
going
to place a expression/function in the datasouce setting of a text box on a
form, the control name SHOULD NOT conflict with an existing field name
that
is behind the report data source (ie: the reports underlying reocrdset).

Aha. Might this, then, account for the fact that my copied report item
(named Text11) worked, whereas the original one (named [bonus]), gave me
problems?

For the most part, most developers do allow the text box control on a
report
to be the same name as the field name (this is the default). This works
when
you need to display a field from the underlying recordsource of the
report.
However, if you name a text box the same as a field, and then start typing
in expressions into the data source of this text box, then things get real
messed up real fast. So, keep this in mind.

Thank you, thank you!

You will need to use a text name, not the name of the actually data field
to
reference the value in a report. Unlike forms in which your code (and text
boxes) can reference any underlying field (even without a text box
control).
However, in a report, this is NOT the case, and you need to use a text box
to reference the data.

=sum([bonus]) in the footer of the report works fine.

Sure, ok...but, what is the name of the text box control in above?

Further, you do realize that event code in a report can only access the
text
box controls in that event?

(eg: code run in the format event for the reports heading can only
use/reference text box controls in the reports header). (note I
said code, since expressions in text boxes do NOT have this limitation).

This is the text of the function written by me that I'm trying to use.

How are you (where) are you using the expression?

fncTranslateTokens = Format(gdc_TokenValue * adc_Tokens, "$#,##0.00")

I don't see where you set the value of gdc_TokenValue ?

I didn't attempt to explain everything - just give context for what I was
talking about. I think you've solved it.