How to set Oracle date to null with ODBC?

Does anyone know how to set an Oracle date field to null using ODBC?  

My code statement is the following:
rs.Fields("BillingDate") = IIf(IsNull(Me.txtBillingDate), Null, Trim(Me.txtBillingDate))

I get an error everytime it gets executed.  The Errors collection returns that there was a data type mismatch.   I have read articles that say that you can't assign an Oracle date the null value using ODBC.  However, I have some older code examples where I was able to do this.  Not sure why it isn't working now.  

I don't want to have to use SQLPassThrough queries (Insert, Update statements) because of the time involved.

Any ideas?
Thanks.
mnordAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
Try this:
rs.Fields("BillingDate") = IIf(Len(Trim$(Me.txtBillingDate)) = 0 , Null, Trim(Me.txtBillingDate))

Or better still (easier to read):

Dim sBillingDate As String

SBillingDate = Trim$(Me.txtBillingDate)
If Len(sBillingDate) Then
   rs.Fields("BillingDate").Value = sBillingDate
Else
   rs.Fields("BillingDate").Value = Null
End if

Anthony
0
 
mnordAuthor Commented:
I am using VB6 with DAO and Oracle 8i.
0
 
DanRollinsCommented:
Hi mnord,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Accept acperkins@devx's comment(s) as an answer.

mnord, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you.  DO NOT accept this comment as an answer.

EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
==========
DanRollins -- EE database cleanup volunteer
0
 
SpideyModCommented:
per recommendation

SpideyMod
Community Support Moderator @Experts Exchange
0
All Courses

From novice to tech pro — start learning today.