NULL Values, how to capture them in Date field

[cacalano]

Hi. I have a bit of code to look for any records with NULL. What I need is this. Check first if the EOS_Date field contains NULL. If so, show "eos" text and exit the IF statement. Error trapping I'd say is what I'm doing here. Instead, it's doesn't trap the NULL field and send it to the calculation line (after 2nd ELSE).

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 %>

 

[Spork52]

Are you looking for empty text fields or for nulls or for both?

If you are looking for both, and the data comes from SQL Server, you could use:

ISNULL(fieldname, "") AS fieldname

in your SQL statement. That will retrieve nulls as empty text which should match "" in your code.

 

[Spork52]

Oops ... Sorry, that should be single quotes in the SQL

 

[Spork52]

Hmm. It doesn't work. The null turns into Jan 1 1900 12:00AM. You have to use a CAST statement first. Something like:

ISNULL(CAST(fieldname as varchar),'') as fieldname