Help Avoid Invalid Use of Null Error

[pwinters]

How can I display something other than this Invalid Use of Null error? I know there is no data in the database for what I've searched on, but I don't want the user to see this error. I'd rather see something like "No Results Found for your Search".


Here is some of my code:
These fields are pulled from a form, and if the user selects an option that has no data associated with it, the "Invalid Use of Null" System Error appears. Can I get around this?

<%
fragid = request.getParameter(&quot;fragid&quot;
String fragval;
if ((fragid == null) || (fragid.equalsIgnoreCase(&quot;null&quot))
fragval = &quot;&quot;;
else fragval = &quot; and Dashboard.fragment_id =&quot; + fragid;
String relval;
if ((releaseid == null) || (releaseid.equalsIgnoreCase(&quot;null&quot))
relval = &quot;&quot;;
else relval = &quot; and Dashboard.BRMonth_Id = &quot; + releaseid;
String yrval;
if ((yearid == null) || (yearid.equalsIgnoreCase(&quot;null&quot))
yrval = &quot;&quot;;
else yrval = &quot; and Dashboard.BRYear_Id = &quot; + yearid;


String total=
&quot;SELECT Format(Clng(Sum(Dashboard.Recycled_Usage)), '#,###') AS SumOfRecycle, &quot; +
&quot;Format(Clng(Sum(Dashboard.Usage)), '#,###') AS SumOfUsage, &quot; +
&quot;Format(Clng(Sum(Dashboard.Bit_amt)), '#,###') AS SumOfBit &quot; +
&quot;FROM Dashboard &quot; +
&quot; WHERE Dashboard.Production_Id > 0&quot; +
fragval +
relval +
yrval;

...
...

%>

Thanks

 

[KenReay]

Hi

Are you aware of the NZ() function,

eg Nz(MyCol,&quot;No Data Present&quot

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[pwinters]

No, I haven't heard of this function, but I'll definitely look into it. Thanks for your help.

 

[pwinters]

Ken,

I changed the Select Statement to:

SELECT
Nz(Format(Sum(Dashboard.Recycled_Usage),'#,###'),0) AS SumofRecy,
Nz(Format(Sum(Dashboard.Usage),'#,###'),0) AS SumofUsg,
Nz(Format(Sum(Dashboard.Bit_Amt),'#,###'),0) AS SumofBit
From Dashboard
...

Now, when the value is NOT Null, I get the correct data, but when it is NULL, I get &quot;&quot; or blanks. If I take out the Format() function, it gives me 0, but I need to format the values when there is data there to make it more readable for the user.

Any suggestions?

Thanks

 

[KenReay]

hi

Format(Sum(Nz(Dashboard.Recycled_Usage,0)),'#,###') AS SumofRecy

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[pwinters]

Hi,

That didn't work. It gives me an error of:

Undefined function 'Nz' in expression

 

[VBRookie]

That means you probably have outdated data access objects.