Re: Can't use function in a report
- From: "Laurel" <FakeMail@xxxxxxxxxxx>
- Date: Sat, 16 Sep 2006 19:40:04 -0400
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?
"J. Goddard" <jrgoddard@xxxxxxxxxxxxxxxxxx> wrote in message
news:%23VLGVYa2GHA.4756@xxxxxxxxxxxxxxxxxxxxxxx
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 wrote:
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
.
- References:
- Can't use function in a report
- From: Laurel
- Re: Can't use function in a report
- From: J. Goddard
- Can't use function in a report
- Prev by Date: Re: filter vs. query on a lan
- Next by Date: How to return information from a form?
- Previous by thread: Re: Can't use function in a report
- Next by thread: Re: Can't use function in a report
- Index(es):
Relevant Pages
|
