Fetch Command with Joined Q+E File Returns Blanks

When an Excel macro that fetches data from a Q+E Query file with a one-to-many join, the query will show blanks instead of duplicate records. These blanks will cause the empty cells to be formatted as text when the data is placed into the Excel Worksheet.

This may pose problems when running an ISBLANK() Macro loop or executing other Excel commands.

Workaround

Delete the blanks in the Excel file or modify the test in the loop to check for empty quotes instead of ISBLANK().

More Information:

The fetch command actually writes "" or a NULL text value into the cell and changes the status of the cell to not blank. In order to change the status of this cell to empty, the cell must be cleared or deleted.

Steps to Reproduce Problem

Run an Excel Macro to:

1. Execute QE.

   chan=INITIATE("QE","System")

2. Open a file and join two files in a one-to-many relationship in Q+E, (leaving blank fields instead of a duplicate field). An example of the results of this join can be viewed on page 15 of the Q+E for Microsoft Excel User's Guide.

   =EXECUTE(chan,"[OPEN('SELECT DEPT_ID, DEPT_NAME, LOC_ID, MGR_ID, FIRST_NAME, LAST_NAME, EMP_ID, HIRE_DATE, SALARY, EXEMPT, INTERESTS FROM C:\EXCEL\QE\DEPT.DBF, C:\EXCEL\QE\EMP.DBF WHERE DEPT.DEPT_ID = EMP.DEPT')]")

3. Fetch the information into an Excel spreadsheet.

   =EXECUTE(chan,"[FETCH('EXCEL','SHEET1','R1:R100','ALL')]") =TERMINATE(chan) =RETURN()

4. Check the blank cells in the Excel spreadsheet by choosing CTRL+F2 or Window Show Info and selecting Value. The value will show as double quotes, indicating that the cell is not empty but simply blank.

"Microsoft Excel Function Reference," version 3.0, pages 70, 130-131, 135

"Q+E for Microsoft Excel User's Guide," version 3.0, pages 82, 96