Solved

Function fails if it has Null paramater

Posted on 2009-05-17
6
393 Views
Last Modified: 2012-05-07
I have a function within a crystal report file.  See code below.
Basically, the problem I have is that if ANY of the parameters been passed into the function are Null, then the whole formula just fails. Doesn't report any issues, just returns null.  Even when the parameter is not actually been used within the function.
I haven't done much of this stuff before so it's probably something small.

The below code will return the word Test every time as long as the parameters are not null.  If any of them are null, then it returns nothing.
Function GetReading(Low As Number, High As Number, PInput As Number, OutPut As String, MEAS As String, TOLERANCE as String, TOLVALUE as Number, DECIMALP as Number)
 

    Dim dRange      As Number

    Dim Reading     As Number

    Dim LowRange    As Number

    Dim HighRange   As Number

    Dim ErrRange    As Number

    Dim PerError    As Number

    Dim strFmt      As String

    Dim strSQL      As String

    Dim intPlace    As Number

    Dim strType     As String

    

    dRange = High - Low

    PerError = TOLVALUE

    strType = trim(TOLERANCE)

    intPlace = 0

    intPlace = DECIMALP

    

    ...

    ...

    getreading = 'Test'

End Function

Open in new window

0
Comment
Question by:obrienj
  • 3
  • 2
6 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 24406590
You need to say what you expect to happen if any of the values are null.

But if there can be nulls in the data then you have two options.
Either set the report options to convert all nulls to default values  ("" or 0 for strings and numbers)
or
test for nulls in the data first before you try to use the field value.
0
 

Author Comment

by:obrienj
ID: 24406622
Are you talking about within the function or within the report???

I can't convert null to values, because it would be wrong. e.g. 0 is a value I would use in the function, whereas Null is not.  If the value is null and I convert it to 0 then the function will return the wrong result.

In the test code I am not even using the values. btw, I commented out the assignations at the start.  See the code below again.  The function does nothing but return the word "Test".  No parameter is used but it still fails if any of them are Null.
Therefore I cant actually test for nulls in the function.  


Function GetReading(Low As Number, High As Number, PInput As Number, OutPut As String, MEAS As String, TOLERANCE as String, TOLVALUE as Number, DECIMALP as Number)

 

    Dim dRange      As Number

    Dim Reading     As Number

    Dim LowRange    As Number

    Dim HighRange   As Number

    Dim ErrRange    As Number

    Dim PerError    As Number

    Dim strFmt      As String

    Dim strSQL      As String

    Dim intPlace    As Number

    Dim strType     As String       

    ...

    ...

    getreading = 'Test'

End Function

Open in new window

0
 
LVL 100

Expert Comment

by:mlmcc
ID: 24406642
Agree.  Crystal halts evaluation of a formula when a NULL value is encountered and it needs too use it.  The exception to this is the IsNull test.

Is this within the application calling the report or is it in a UFL that Crystal calls?

mlmcc
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:obrienj
ID: 24406651
It's one of the Report Custom Functions.
So, basically, I do have to set the parameter value to something at all times?  I can't do the isnull within the code of the function itself.  
That sucks.
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 24407111
I haven't written a custom function.  You can try an IsNull and then return a value or evaluate differently

Another idea would be to use a default value for the null parameters and test for that.

mlmcc
0
 

Author Comment

by:obrienj
ID: 24409866
Can't use ISNULL within a custom function for some bizarre reason.  Think I will try using a default value (999) or something.  
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Combine and display field 5 73
Change start of Week from Sunday to Friday 8 47
across tab - Distinct Count - Crystal Reports 2 76
business objects to connect to MSSQL 6 61
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now