PRB: Error 3162 Setting FoxPro Date Field to Null (168837)

MORE INFORMATION

In Visual Basic and other VBA and DAO applications, dates are stored as the number of days since 12/30/1899. This date is stored internally as the number 0. The Visual FoxPro ODBC driver takes this 0 value to represent an empty date.

Steps to Reproduce Behavior

1. Create and execute the following Visual FoxPro program:

         CREATE DATABASE ztest
         CREATE TABLE ztest (dfield d)
         APPEND BLANK
         REPLACE dfield WITH {3/15/96}
         USE
   						

2. In Visual FoxPro, modify the Database "ztest" and add the table "ztest" to the database. Add an additional character field and create a Primary Key (this is required for the ODBC driver to write back to the table). Exit Visual FoxPro and create a Visual FoxPro data source called ztest, which points to the database ZTEST.DBC.
3. Create a new Visual Basic or VBA project. Make sure that Microsoft DAO 3.0 or 3.5 object library is included in your References.
4. Add the following code to a Module:

         Sub Test_VFP_Date()
         Dim db As Database, rs As Recordset
           Set db = OpenDatabase("", False, False, _
                    "ODBC;database=testdata;uid='',pwd=;dsn=ztest")
           Set rs = db.OpenRecordset("ztest")
           rs.Edit
           rs!dfield = Null
           rs.Update
         End Sub
   						

5. Run the code from the Immediate Window. If prompted for ODBC connection information, select the ztest data source. Then, select the ztest table and click OK.
6. You will receive the run-time error described above for the line:
   rs!dfield = Null
7. Change the line to either:

   rs!dfield = #12/30/1899#

   -or-

   rs!dfield = 0

   and rerun the application.
8. Open the ztest table in Visual FoxPro to verify the date is now blank.