Around the dateFormat() call, add a CFIF that'll use isDate() to to decide
if it should do the dateFormat(), or just insert NULL. You can also use
CFQUERYPARAM for the same task:
<cfif isDate(act_date)>#dateFormat(act_date,
"mm/dd/yyyy")#<cfelse>NULL</cfif>
Or
<cfqueryparam cfsqltype="cf_sql_date" value="#act_date#" null="#NOT
isDate(act_date)#" />
> -----Original Message-----
> From: Mickael [mailto:[EMAIL PROTECTED]
> Sent: Monday, December 15, 2003 10:10 AM
> To: CF-Talk
> Subject: Problems with Nulls
>
> Hello All,
>
> I am running in to a problem on something that I do locally
> on our intranet. I query one database(SYBASE) then load the
> data that I need in to an access database for a CF app that I
> have created.
>
> There is section on the Sybase database where notes are
> entered then notes tables looks like this
>
> AccountNumber
> NoteDate
> Notetime
> Note
>
> Then I loop over all these records and insert them in to my
> access database it works fine until I hit a record that has no date
>
> I get this error
>
> Error Diagnostic Information
> ODBC Error Code = 22005 (Error in assignment)
>
>
> [Microsoft][ODBC Microsoft Access Driver] Data type mismatch
> in criteria _expression_.
>
>
> SQL = "Insert into Client_comm (AccountNumber, Act_date,
> Act_time, Comments) Values (1081729, '','', '+----Match
> Search (by SIN Number)-------')"
>
> In my access database the fields are as follows
> AccountNumber(Number), Act_date(date) Act_Time(text), Comments(text)
>
>
>
>
> My Insert statement looks like this
>
> <cfquery name="Act_InsetNotes" datasource="#dsn#">
> Insert into Client_comm
> (AccountNumber, Act_date, Act_time, Comments)
> Values
> (#AccountNumber#, '#dateformat(act_date,
> "mm/dd/yyyy")#','#timeformat(act_time, "hh:mm:ss")#', '#comments#')
> </cfquery>
>
> Thanks
>
> Mike
>
>
>
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
- Problems with Nulls Mickael
- Re: Problems with Nulls Barney Boisvert
- Re: Problems with Nulls Mickael
- RE: Problems with Nulls Barney Boisvert
- Re: Problems with Nulls Mickael
- Problems with Nulls Mickael
- RE: Problems with Nulls Janine Jakim