XL: Can't Apply Number Format to Data Returned by SQL.REQUEST

ID: Q131236

The information in this article applies to:

Microsoft Excel for Windows, versions 5.0, 5.0c
Microsoft Excel for Windows 95, version 7.0
Microsoft Excel for Macintosh, versions 5.0, 5.0a

SUMMARY

In Microsoft Excel 5.x or 7.0, if you use the XLODBC.XLA function SQL.REQUEST to return data to your worksheet, you cannot apply a number format to the data by choosing the Number command from the Format menu.

Using SQL.REQUEST creates a "link" to the data source and the data is returned as text--thus, the data cannot be reformatted using a number format. This is by design in Microsoft Excel 5.x and 7.0.

MORE INFORMATION

To work around this limitation, you may use the TEXT function to reformat the data. For example, the following formula returns the text "6/5/1987":

   =SQL.REQUEST("DSN=NWind",,,"SELECT Hire_Date FROM Employee WHERE
   Last_Name = 'Davolio'")

This formula can be modified so that the date is displayed in a different Date format. For example, the following formula returns the text "Jun-05- 1987":

   =TEXT(SQL.REQUEST("DSN=NWind",,,"SELECT Hire_Date FROM Employee WHERE
   Last_Name = 'Davolio'"),"mmm-dd-yyyy")

Additional query words: 5.00 5.00a 5.00c 7.00

Keywords          : xlformat 
Version           : WINDOWS:5.0,5.0c,7.0; MACINTOSH:5.0,5.0a
Platform          : MACINTOSH WINDOWS
Issue type        : kbprb

Last Reviewed: February 5, 1998