NULL Values, going nuts here

Hi. I have a bit of code to look for any records with NULL. What I need is this. If the EOS_Date field is NULL, don't do the calculation. Check first if the 'EOS_Date'field is NULL, if so, Just display "eos" and exit the IF statement. Error trapping I'd say is what I'm doing here.

When I test using a different field in the same table, I get the expected results, so my if structure is ok. So, it's something to with checking for NULLS. Syntax error possibly?

thanks in advance. please help. tx.

<% If TRIM(FP_FieldVal(fp_rs,"Codes")) = "UKRP" _
OR TRIM(FP_FieldVal(fp_rs,"Codes")) = "EEP" _
OR TRIM(FP_FieldVal(fp_rs,"Codes")) = "UK" Then %>

<%Else%>


<% If TRIM(FP_FieldVal(fp_rs,"EOS_Date")) = "" Then %>
<td nowrap><font size="2" face="Arial">no eos date

<%Else%>

<td nowrap><font size="2" face="Arial"><%=FormatCurrency(FP_FieldVal(fp_rs,"Ref_Price")* FP_FieldVal(fp_rs,"EOS-Today") /FP_FieldVal(fp_rs,"EOS-HP_INTRO")) %></font></td>

<%End If %>
<%End If %>

 

ALso wanted to mention I get an error now because the IF statement doesn't capture the null, then send the NULL to the calculation. That's where is bombs. thanks again.

 

try
<% If TRIM(FP_FieldVal(fp_rs,"EOS_Date")) = "" or TRIM(FP_FieldVal(fp_rs,"EOS_Date")) = "null" Then %>

write the exact error you get

 

Post the error message. Also, a NULL is not going to equal a zero-length string. Have you tried using the VBScript IsNull(expression) function?

 

Try

Replace
<% If TRIM(FP_FieldVal(fp_rs,"EOS_Date")) = "" Then %>
With

<% If Not IsNull(FP_FieldVal(fp_rs,"EOS_Date")) Then %>

 

Do this in the database, by using the EOS_Date column in the calculation. Null is unknown ... it's the absence of any value. Null doesn't equal anything ( including null ) and when you use it in any calculation, the result is unknown / doesn't have a value. So, database engines return null when a null value is used in a calculation.

Try it:

Select null * 5

Code (markup):