Steve and Shaun,

Thank you for your help!

Updating the date with set mydate=null does the trick.

Thank you very much for your speedy responses to my question!

PAul

  ----- Original Message ----- 
  From: Steve Howard (PFE)<mailto:sthoward@[...].com>  
  To: Shaun Wallace<mailto:shaunwallace@[...].com>  ; Paul Davison<mailto:p_c_davison@[...].c
om>  ; perl-win32-database@[...].com<mailto:perl-win32-database@[...].com> 
  Sent: Monday, March 24, 2008 9:55 AM
  Subject: RE: Clearing the contents of a Datetime field


  The # is specific to access, and in fact will not work with SQL. You can update the field 
to null if the field in nullable in order to clear a date, though.

  What I would usually do to accomplish this is use a prepared statement something like this
 (assuming $dbh is a valid database handle:

  My $sql = $dbh-> prepare(q{ mytable set mydate=null where mydate = ?}) || die "Can't prepar
e: $DBI::errstr";
  $sql-> execute('2008-03-23') || die "Can't execute: $DBI::errstr";


  Or to update to clear it (null it) you could change the execute line to this:

  $sql-> execute(undef) || die "Can't execute: $DBI::errstr";

  Steve Howard

  -----Original Message-----
  From: perl-win32-database-bounces@[...].com<mailto:perl-win32-database-bounces@[...].com>  
[mailto:perl-win32-database-bounces@[...].com] On Behalf Of Shaun Wallace
  Sent: Monday, March 24, 2008 9:47 AM
  To: Paul Davison; perl-win32-database@[...].com<mailto:perl-win32-database@[...].com> 
  Subject: RE: Clearing the contents of a Datetime field

  I have not used ODBC to write dates to Access, however I know that when
  querying and updating dates in Access you need to surround dates with
  #'s such as #2008-03-24 12:30:26 PM#.  Also to "clear" a date with
  Access you simply update the date field to Null.  I assume you would use
  the same idea with ODBC.

  See if this works via ODBC:

  $sql = "update mytable set mydate=null where mydate=#2008-03-23#"

  Thanks,
  Shaun

  ________________________________

  Moore & Van Allen <http://www.mvalaw.com/<http://www.mvalaw.com/> >

  Shaun K. Wallace
  Data Analyst / Paralegal

  Suite 4700
  100 North Tryon Street
  Charlotte, NC  28202-4003






  ________________________________

          From: perl-win32-database-bounces@[...].com<mailto:perl-win32-database-bounces@[..
.].com> 
  [mailto:perl-win32-database-bounces@[...].com] On Behalf
  Of Paul Davison
          Sent: Monday, March 24, 2008 12:14 PM
          To: perl-win32-database@[...].com<mailto:perl-win32-database@[...].com> 
          Subject: Clearing the contents of a Datetime field


          I am using ODBC to work on data in an Access database and need
  to find a way to clear a date/time field. I have tried writing
  "0000-00-00 00:00:00" and "" into it and I get back Error: [-3030] [1]
  [0] "[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in
  criteria expression."

          Is there a method to clear the contents of a Date/time field or
  set it to a null value?

          Paul


  --------------------------------------------------------------------------
  To comply with certain U.S. Treasury regulations, we inform you that, unless expressly sta
ted otherwise, any U.S. Federal tax advice contained in this e-mail, including attachments, 
is not intended or written to be used, and cannot be used, by any person for the purpose of 
avoiding any penalties that may be imposed by the Internal Revenue Service.
  --------------------------------------------------------------------------
  CONFIDENTIAL & PRIVILEGED
  Unless otherwise indicated or obvious from the nature of the following communication, the 
information contained herein is attorney-client privileged and confidential information/work
 product. The communication is intended for the use of the individual or entity named above.
 If the reader of this transmission is not the intended recipient, you are hereby notified t
hat any dissemination, distribution or copying of this communication is strictly prohibited.
 If you have received this communication in error or are not sure whether it is privileged, 
please immediately notify us by return e-mail and destroy any copies, electronic, paper or o
therwise, which you may have of this communication.
  --------------------------------------------------------------------------

  _______________________________________________
  Perl-Win32-Database mailing list
  Perl-Win32-Database@[...].com<mailto:Perl-Win32-Database@[...].com> 
  To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs<http://listserv.activestate
.com/mailman/mysubs>