Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1502
  • Last Modified:

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.
0
mnord
Asked:
mnord
1 Solution
 
mnordAuthor Commented:
I am using VB6 with DAO and Oracle 8i.
0
 
Anthony PerkinsCommented:
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now