Using DB.SQL.QUERY with Type 2 Requires Double Single Quotes

 Last reviewed: November 3, 1994
Article ID: Q70067

SUMMARY

The command DB.SQL.QUERY(), provided by the add-in QE.XLA, allows you to send SQL queries to Q+E. The query is executed by Q+E and the results are returned to Excel.

When sending a query that contains a text comparison criteria, it is necessary to place the criteria inside double single quotation marks. (for example, ``NC''). Without the double single quotation marks, the command will return #REF.

MORE INFORMATION

DB.SQL.QUERY(type,query,destination,filename,linked)

The first argument, "type", is a number from 1 to 3 indicating the type of query to be executed.

  • Type 1 specifies that the previous query is to be executed.
  • Type 2 executes the text string specified by the argument "query".
  • Type 3 executes a query contained in the file specified by "filename".

If "type" is 2 and you use a WHERE clause to limit your extract to only records that match certain criteria in the form of text, you must enclose the text value in two sets of single quotation marks or two sets of double quotation marks.

The following example:

  • Initiates a DDE channel with Q+E
  • Activates Sheet1 in Excel
  • Selects all records in ADDR.DBF where the state is equal to "NC"
  • Pastes those records into Sheet1, starting with the active cell

For this macro to work correctly, you must:

  1. In A3, change the path to the actual location of ADDR.DBF.

  2. Change A2 to indicate an open worksheet.

  3. The add-in QE.XLA must be loaded.

  4. The third line should all be entered into the same cell. It is split into two lines here for readability.

  5. Place two single quotation marks to the left of "NC" and two single quotation marks to the right, followed by a double quotation mark that matches the double quotation mark before "select".

  6. You may also use two sets of double quotation marks instead of two sets of single quotation marks.

Example

A1 =INITIATE("qe","system") A2 =ACTIVATE("sheet1") A3 =DB.SQL.QUERY(2,"select * from dBASEfile | d:\excel\xl3\qe\addr.dbf 

    where STATE=''NC''",1)
A4 =RETURN()

REFERENCES

"Microsoft Q+E for Excel User's Guide." Version 3.00, pages 68-71. "README.TXT", Microsoft Excel version 3.00, Section 3.

KBCategory: kbother
KBSubcategory:

Additional reference words: qpluse

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: November 3, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.