Error in SQL query (coldfusion)

[WillowFae]

I have the following query in my page. The idea is that if a field contains an entry I want it to update. If not, leave it marked as NULL. This is because the fields are dates and if I don't say to leave it as NULL then it just displays as 01-01-01 which I don't want it to do.

The relevant bit of the code is


update tblThesis set
thesisCopy1Recd =
<cfif Trim(FORM.frm_copy1Recd) NEQ "">
'#dateformat(FORM.frm_copy1Recd,"dd-mmm-yy")#'
<cfelse>
NULL
</cfif>



Someone told me that I couldn't use = with NULL so I should change the code to the following


update tblThesis set
thesisCopy1Recd
<cfif Trim(FORM.frm_copy1Recd) NEQ "">
='#dateformat(FORM.frm_copy1Recd,"dd-mmm-yy")#'
<cfelse>
IS NULL
</cfif>



With the first example, I got an occasional error saying that there was incorrect syntax near =

With the second example, I am getting a permanent error that there is incorrect syntax near keyword IS.

Anyone got any thoughts? Or alternatively (which would be even better) how to get around the date issue in the first place. With the system the way I have it, the user can't delete a date from a field without it defaulting to 01-01-01. Ideally I'd like them to be able to.

Thanks

[SteelValor]

You could try

update tblThesis set
thesisCopy1Recd
<cfif isDate(FORM.frm_copy1Recd)>
='#createODBCDateTime(FORM.frm_copy1Recd,"dd-mmm-yy")#'
<cfelse>
= ''
</cfif>

or in your output do

<cfif query.thesisCopy1Recd is not "01/01/01">
#dateformat(frm_copy1Recd,"dd-mmm-yy")#
<cfelse>
</cfif>

Hope this helps you out :)