PRB: Error 3162 Setting FoxPro Date Field to Null

Last reviewed: September 30, 1997
Article ID: Q168837
The information in this article applies to:
  • Microsoft Visual Basic Professional and Enterprise Editions for Windows, version 5.0
  • Microsoft Visual Basic Professional and Enterprise Editions, 32-bit only, for Windows, version 4.0
  • Microsoft Visual FoxPro for Windows, versions 3.0, 5.0
  • Microsoft Visual Basic for Applications (VBA) included with: - Microsoft Access versions 7.0, 97 - Microsoft Excel, versions 7.0, 97 for Windows - Microsoft Word 97 for Windows - Microsoft PowerPoint 97 for Windows

SYMPTOMS

Trying to set a Visual FoxPro Date field to Null via DAO/ODBC results in the following error:

   "Run-time error '3162':

   Null Is Invalid."

CAUSE

The FoxPro ODBC driver does not use Null for an empty date.

RESOLUTION

Set the date field to 12/30/1899 instead. The Visual FoxPro ODBC driver translates this value into an empty date.

STATUS

This behavior is by design.

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.

REFERENCES

For more information, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q150433
   TITLE     : PRB: 12/30/1899 Appears as a Blank Date in Microsoft Access
Keywords          : APrgDataODBC vb432 VB4WIN vb5all VBKBDB VBKBJet vbwin GnrlVb kberrmsg kbprg
Technology        : kbvba
Version           : WINDOWS:3.0 4.0 5.0
Platform          : WINDOWS
Issue type        : kbprb


================================================================================


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: September 30, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.