DOCUMENT:Q192308  27-JUL-2001  [foxpro]
TITLE   :PRB: View Designer Misreads Date Criteria in ODBC Date Format
PRODUCT :Microsoft FoxPro
PROD/VER:WINDOWS:2.5,3.0,3.0b,5.0,5.0a,6.0
OPER/SYS:
KEYWORDS:kbDesigner kbSQL kbvfp300 kbvfp300b kbvfp500 kbvfp500a kbvfp600 kbMDAC250 kbSQLProg

======================================================================
-------------------------------------------------------------------------------
The information in this article applies to:

 - Microsoft Visual FoxPro for Windows, versions 3.0, 3.0b, 5.0, 5.0a, 6.0 
 - Microsoft Data Access Components version 2.5 
-------------------------------------------------------------------------------

SYMPTOMS
========

After saving and closing a remote view with a date criteria in the Open Database
Connectivity (ODBC) Date Format, the View Designer does not read the criteria
correctly. For example:

   {d 'yyyy-mm-dd'}

RESOLUTION
==========

Use the Create SQL View command to create and modify views. For an existing
view, the following code can be used to capture the existing SQL statement:

      MyFile=CURDIR() + 'sqltxt.txt'
      SET ALTERNATE TO &MyFile ADDITIVE
      SET ALTERNATE ON
      ?DBGETPROP("Test1","View","SQL")
      SET ALTERNATE TO
      SET ALTERNATE OFF
      MODI FILE &MyFile NOWAIT

One other option is to modify the view in the View Designer. This necessitates
entering the date criteria again before saving.

STATUS
======

Microsoft is researching this problem and will post new information here in the
Microsoft Knowledge Base as it becomes available.

MORE INFORMATION
================

In Visual FoxPro 6.0, this problem is further exacerbated by the new setting,
SET STRICTDATE. The default setting of SET STRICTDATE causes the following error
when trying to open the view definition in the View Designer:

   Date/DateTime contains illegal characters.

To avoid this error and modify the view in the View Designer, issue the following
command from the Command window:

      SET STRICTDATE TO 0

Please note that to save any changes in the View Designer to this remote view,
the date criteria must be re-entered. This is true for all versions of Visual
FoxPro listed in the "Applies to" section of this article.

Steps to Reproduce Behavior
---------------------------

1. Create a data source to a SQL Server Pubs database where you have
   permissions.

2. Run the following code in a program file:

         CREATE DATABASE test
         CREATE SQL VIEW Test1 REMOTE CONNECTION MyPubsDSN AS ;
            SELECT * FROM sales WHERE ord_date > {d '1993-12-30'}
         USE Test1
         BROWSE NOWAIT
         *SET STRICTDATE TO 1 && Uncomment For Visual FoxPro 6.0.
         MODIFY VIEW Test1
         MESSAGEBOX(DBGETPROP("test1","view","sql"))
         *SET STRICTDATE TO 0 && Uncomment For Visual FoxPro 6.0.
         *MODIFY VIEW Test1 && Uncomment For Visual FoxPro 6.0.

   Notice that the date criteria displayed in the View Designer is:

   { / / }

   It should be:

   {d '1993-12-30'}

Note that the message box displays the correct criteria.

Additional query words:

======================================================================
Keywords          : kbDesigner kbSQL kbvfp300 kbvfp300b kbvfp500 kbvfp500a kbvfp600 kbMDAC250 kbSQLProg 
Technology        : kbVFPsearch kbAudDeveloper kbMDACSearch kbMDAC250 kbVFP300 kbVFP300b kbVFP500 kbVFP600 kbVFP500a
Version           : WINDOWS:2.5,3.0,3.0b,5.0,5.0a,6.0
Issue type        : kbprb
Solution Type     : kbpending

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

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.

Copyright Microsoft Corporation 2001.