How to pass a subreport field to a standard module function?

**TheSmileyCoder** · Jun 16 '12, 08:57 PM

There are a few things I find a bit confusing with the code samples you have provided. First off, I dont know of any IsNothing function in access, so either you have typed it wrong, or you have defined such a function yourself.

Secondly, your last bit of code takes "prog" as input parameter yet does a select case on "Program". You should ALWAYS have Option Explicit at the top of all your modules.

With your first bit of code, I just wanted you to know there is a simpler way of doing it, using the format value for the textbox.

If the textbox is bound to a numeric field (Which I presume ABC is) you can specify how it should format positive, negative, zero and null (in that order). An example shown below:

Code:

Format: #;(#);"Zero";"Null"

That will make positive numbers be shown normally, negative numbers be shown with a parenthesis around them and 0 be shown as a textstring "Zero" and null as a textstring "Null". This can be quite handy. Note that it does in no way change the value of the data stored in the field, and should I reference textbox txABC bound to field ABC with a value of 0 for example:

Code:

Msgbox "Textbox txABC contains:" & me.txABC

it would report "Textbox txABC contains:0" and NOT "Textbox txABC contains:Zero"

Im sorry however to say, that I simple don't understand what your caveat is about.
I think what you want to accomplish should be done at the query level instead of in the report, and you should look to solve it there, or return with more information for me or someone else to assist you.

**beacon** · Jun 16 '12, 10:45 PM

Thanks for taking the time Smiley...I'll try to clarify a few of the things you had questions about.

There are a few things I find a bit confusing with the code samples you have provided. First off, I dont know of any IsNothing function in access, so either you have typed it wrong, or you have defined such a function yourself.

IsNothing is a custom function I use that checks for null, empty, zero, and a couple of other conditions that might cause a value to be "nothing", per se.

Secondly, your last bit of code takes "prog" as input parameter yet does a select case on "Program". You should ALWAYS have Option Explicit at the top of all your modules.

This was a typo that I missed when asking my question. I had tried a bunch of different things and failed to change prog to Program, or vice versa. Either way, whether I used prog in both places or Program in both places, my function still did not work. I always use Option Explicit...unfo rtunately there's no Option Explicit when typing our questions here in Bytes to prevent my mistake! =)

With your first bit of code, I just wanted you to know there is a simpler way of doing it, using the format value for the textbox.

If the textbox is bound to a numeric field (Which I presume ABC is) you can specify how it should format positive, negative, zero and null (in that order). An example shown below:

Format: #;(#);"Zero";"N ull"

I probably misstated the part about ABC being bound. It's actually a field on a cross tab query, where "ABC" is a column heading in the cross tab's properties, and the value is a calculation...a count of documents that weren't completed within a timeframe.

With that in mind, I appreciate the formatting tip, however, this part of my code actually works fine. It's the numerator in the division I use in the expression and I just needed it to be zero if the value was nothing. The output of the other text box I'm working with, that uses the rounding expression, needs to be formatted as percent (which is the main reason why I created the expression that rounds the value in the first place). The reasoning for it was to force a value to appear as zero percent on the report if the value is "nothing". Your suggestion may very well work and I look forward to giving it a test, but for the time being, I need to focus on the text box with the rounding expression.

Im sorry however to say, that I simple don't understand what your caveat is about.

My objective is to turn the following expression into a standard module function so that I can reuse it without having to type so much into so many text boxes:

Code:

//This is basically an expression that checks for a value
//or rounds to a certain number of decimal places.
=IIf(([Col1]=0) And ([srptTimelinessTotalsByProgram].[Report]![ABC]=0 Or 
IsNothing([srptTimelinessTotalsByProgram].[Report]![ABC])),0,
IIf(([Col2]/[srptTimelinessTotalsByProgram].[Report]![ABC]>0.00000001) And 
([Col1]/[srptTimelinessTotalsByProgram].[Report]![ABC]<0.05),([Col1]/[srptTimelinessTotalsByProgram].[Report]![ABC])+0.005,
[Col1]/[srptTimelinessTotalsByProgram].[Report]![ABC]))

The code basically rounds the result of dividing [ColX] by [srptTimelinessT otalsByProgram].[Report]![Y], where 'X' is a number related to a column on the report and 'Y' is a program field on the subreport.

I'm sure it's probably possible to accomplish what I'm trying to do with the rounding on a query, but I unfortunately don't have the time to rework the queries that are supplying this and other reports.

So, to reiterate my question, is it possible to pass the field on a subreport to a standard module function so that I can access the value and return it to an expression?

The caveat I spoke of occurs when I try to pass the value in the field on the subreport to the standard module function. If I try to pass the following:

Code:

=TimelinessPercent([Col1],[srptTimelinessTotalsByProgram].[Report]![ABC])

...to my slightly revised standard module function so that it looks like this:

Code:

//My attempt at the standard module function
Public Function TimelinessPercent(col As Long, fld As Long)
 
    If (col = 0) And (fld = 0 Or IsNothing(fld)) Then
        TimelinessPercent = 0
    ElseIf (col / fld > 0.00000001) And (col / fld < 0.05) Then
        TimelinessPercent = (col / fld) + 0.005
    Else
        TimelinessPercent = col / fld
    End If
 
End Function

...then the function works for all the text boxes on the report where a value has been entered in for that program (in [srptTimelinessT otalsByProgram].[Report]![ABC]). However, if the value for the program is null, then the output on the report from the standard module function I've used in the expression is #Type. I don't want #Type to display on the report if [srptTimelinessT otalsByProgram].[Report]![ABC] is null.

So, the only way I can determine this would work, since it worked fine when I included the entire expression in the text box, was to somehow pass the subreport field to the standard module function before carrying out the steps to check the value and round it accordingly.

I understand this is a very long, complicated, and probably not ideal situation (or way to handle the situation), but I'm really just interested in determining whether I can pass the subreport field to the standard module function so I can use it to return the value that will display as a percent on the report. If there isn't a way to do that, that's fine...I can always go back to typing the full expression into the text box, but I was hoping to learn something and save myself from having to type so much in the future.

Thanks,
beacon

**TheSmileyCoder** · Jun 18 '12, 09:38 AM

I think I understand better now. I thought you wanted to refer to a record which didn't exist, but now I believe what you want to do is to refer to a FIELD (which can be null at times) on a record.

As I see it now, the problem is that your function expects to receive a Long, and sometimes you pass it Null. That is not acceptable to VBA. This is a case where the Variant datatype can come in handy, or the NZ function.
The NZ([Field],[Value]) will check for null, and replace the null with a safe value of your choice. Example below:

Code:

TimelinessPercent([Col1],Nz([srptTimelinessTotalsByProgram].[Report]![ABC],0))

Now if [srptTimelinessT otalsByProgram].[Report]![ABC] Is not null, the value in the control will be passed. If it IS null, the nz will convert it to 0 before passing it along.

The OTHER way to approach it is to allow your custom function to accept null, by changing the incoming datatype from long to Variant.

Code:

Public Function TimelinessPercent(col As Long, fld As Variant)
    If (col = 0) And (fld = 0 Or IsNothing(fld)) Then
        TimelinessPercent = 0
    ElseIf (col / fld > 0.00000001) And (col / fld < 0.05) Then
        TimelinessPercent = (col / fld) + 0.005
    Else
        TimelinessPercent = col / fld
    End If
 
End Function

Assuming your IsNothing function works properly, you shouldn't need to change anything more.

How to pass a subreport field to a standard module function?

How to pass a subreport field to a standard module function?

Comment

Comment

Comment